locked
select and update the same record RRS feed

  • Question

  • User767034699 posted

    Hi there guys,

    i have this project where a user withdraws funds and then we create a unique account number lets say 1234. if the same user comes back and withdraws i need to update the new record with the same unique account number generated from the first withdrawal. 

    if the user is new i need to skip the update and create that unique number for new users...

    using a stored procedure that has #temp if possible tables how could i achieve this?

    kind regards

    Tony

    Tuesday, May 22, 2018 2:18 PM

Answers

  • User475983607 posted

    i have this project where a user withdraws funds and then we create a unique account number lets say 1234. if the same user comes back and withdraws i need to update the new record with the same unique account number generated from the first withdrawal. 

    The requirement are not clear.   

    If this an accounting feature then create a table where each transaction add a new records, debits and credits, similar to how your checking accounts works. Don't update a record and calculate a result because you'll lose history.

    if the user is new i need to skip the update and create that unique number for new users...

    I assume if the user does not have an login then the user must register before invoking an update.

    using a stored procedure that has #temp if possible tables how could i achieve this?

    Temporary tables (#temp) exist for the current connection.  If you're able to accomplish the required steps in one connection then a temporary table will work otherwise a temporary table will not work.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2018 2:58 PM
  • User753101303 posted

    Hi,

    Seems you want to create a row in the Account table if the user doesn't have already one account? A user should have exactly 1 account, 0 or 1, or 0 to n ?

    For now I'm not sure how it is related to temp ,tables. It seems you should create this once for all in a permanent  table. Basically something such as :

    IF NOT EXISTS(SELECT 1 FROM Account WHERE User=@User) INSERT INTO Account(User) VALUES (@User) :

    if there is not already one account for this user, create an account that belongs to this user. And then all changes on this account are recorded.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2018 3:04 PM
  • User347430248 posted

    Hi tonyR6,

    I check your table design.

    You had mentioned that,"i will only check if that user exists in the account table, but i will also want to keep track of withdrawn amounts"

    First, Why you want to check for user whether he is exist or not. It is better that you let the user to log in in your system first, then after perform any operation for that specific user.

    As I said before, You can create a wallet when user create a new account. So you need to save the 'Wallet_number' in User's details table. 

    You can easily fetch the wallet number using user_id when you want to perform withdrawal.

    I suggest you to create new table to maintain history of withdrawal.

    So I think you need to modify your table design to make it work properly.

    After that, You can insert a new record for that specific user instead of updating the record.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 24, 2018 5:23 AM

All replies

  • User475983607 posted

    i have this project where a user withdraws funds and then we create a unique account number lets say 1234. if the same user comes back and withdraws i need to update the new record with the same unique account number generated from the first withdrawal. 

    The requirement are not clear.   

    If this an accounting feature then create a table where each transaction add a new records, debits and credits, similar to how your checking accounts works. Don't update a record and calculate a result because you'll lose history.

    if the user is new i need to skip the update and create that unique number for new users...

    I assume if the user does not have an login then the user must register before invoking an update.

    using a stored procedure that has #temp if possible tables how could i achieve this?

    Temporary tables (#temp) exist for the current connection.  If you're able to accomplish the required steps in one connection then a temporary table will work otherwise a temporary table will not work.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2018 2:58 PM
  • User753101303 posted

    Hi,

    Seems you want to create a row in the Account table if the user doesn't have already one account? A user should have exactly 1 account, 0 or 1, or 0 to n ?

    For now I'm not sure how it is related to temp ,tables. It seems you should create this once for all in a permanent  table. Basically something such as :

    IF NOT EXISTS(SELECT 1 FROM Account WHERE User=@User) INSERT INTO Account(User) VALUES (@User) :

    if there is not already one account for this user, create an account that belongs to this user. And then all changes on this account are recorded.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2018 3:04 PM
  • User767034699 posted
    Thanks guys for respose
    @mgebhard
    Okay let me explain this clear. We doing integration with payment wallet gateway. So if the user doesn't have a wallet I will send a request to create a wallet. One of the parameters is cell phone number. So they response with a wallet number. Then later I will have to use that wallet number to do a payment. If the same user comes back and request a withdrawal first I will create a new record in the database and insert the same wallet number that the user used for the 1st withdrawal.

    Now let's say I have 200 records to check if some of them have already have wallet number, that's where im getting a bit of a challenge

    Kind regards
    Tony
    Tuesday, May 22, 2018 3:24 PM
  • User767034699 posted
    @PatriceSc

    Oh yes something like this but now we have a table called onetimpin, one of the column being "walletnumber"

    Kind regards
    Tony
    Tuesday, May 22, 2018 3:30 PM
  • User475983607 posted

    Thanks guys for respose
    Okay let me explain this clear. We doing integration with payment wallet gateway. So if the user doesn't have a wallet I will send a request to create a wallet. One of the parameters is cell phone number. So they response with a wallet number. Then later I will have to use that wallet number to do a payment. If the same user comes back and request a withdrawal first I will create a new record in the database and insert the same wallet number that the user used for the 1st withdrawal.

    Now let's say I have 200 records to check if some of them have already have wallet number, that's where im getting a bit of a challenge

     

    This is purely a design issue.  Why do you have 200 records to check?  Are you charging the user monthly?  

    It seems as though you have not spent enough time designing this feature.   Maybe talk with your end users for guidance.

    Tuesday, May 22, 2018 5:01 PM
  • User767034699 posted
    Hi there mgebhard, thanks for the response

    I won't say design issue, but putting some logic on the code.

    Let me put another example how it will go. Let's say user request withdrawal of 50 bucks. I will go create insert into onetime table columns(cell number withdraw date, approved date) . This is a new record entirely and doesnt have a wallet number.

    Now I can go and send a request to the wallet gateway to create a wallet for that user. In my c# code I have a method which will call a stored procedure to select all approved transactions insert into temp table.

    I would like to check if all transactions have a wallet number for all users. If they do update them with that existing wallet number only if that record already has a wallet number

    Kind regards
    Tony
    Tuesday, May 22, 2018 5:31 PM
  • User475983607 posted

    tonyR6

    I won't say design issue, but putting some logic on the code.

    That's like saying, "I'm not lost I just don't know where I am?".

    tonyR6

    Let me put another example how it will go. Let's say user request withdrawal of 50 bucks. I will go create insert into onetime table columns(cell number withdraw date, approved date) . This is a new record entirely and doesnt have a wallet number.

    I would establish and validate the wallet first.  I don't understand the design logic here.  You're accepting payment before knowing the payment is valid or possible?  

    tonyR6

    Now I can go and send a request to the wallet gateway to create a wallet for that user. In my c# code I have a method which will call a stored procedure to select all approved transactions insert into temp table.

    Seems like extra work.  If the wallet fails then you need to delete the records.

    tonyR6

    I would like to check if all transactions have a wallet number for all users. If they do update them with that existing wallet number only if that record already has a wallet number

    I don't understand why you would update previously saved wallets transactions.  I assume a wallet is part of the user account preferences.  IMHO, users should have the ability to select a payment preference.  

    Maybe you should consult the Google wallet documentation for best practices?

    Tuesday, May 22, 2018 5:57 PM
  • User767034699 posted
    Lol @mgebhard

    Nah m not trying to update previously saved wallet transactions. I receive a wallet number after creating a wallet for a user only. I'm saving it to make payments to that user with the bank. I can't create wallet everytime that user makes a withdrawal.

    Tuesday, May 22, 2018 7:12 PM
  • User767034699 posted

    I would establish and validate the wallet first.  I don't understand the design logic here.  You're accepting payment before knowing the payment is valid or possible?  

    Not really, here is how my table looks

    [PIN]
    [Amount]
    [CurrencyID]
    [WithdrawDate]
    [PaymentDate]
    [IsPaid]
    [cell number]
    [walletnumber]
    [isCreated]

    before i can call the bank payment API. User request a withdrawal, from iframe. I insert that record into the database. Now that new user doesnt have wallet yet. what do i do ? i need to call the bank payment API to create that wallet for that user. They then send a response of successful creation of wallet along with the wallet number. then i go and update that record inserting the walletnumber.  If the same user comes back and request another withdrawal. I dont need to query the API for creating a wallet. i still need to create a second insert for the same user as its a different transaction. But now that record doesnt have a wallet number. 

    now using the 1st record i need to update the second record with the wallet number that was created for that user.... please see below example

    [PIN][Amount][CurrencyID][WithdrawDate][PaymentDate][IsPaid][cell number][walletnumber]
     123  50          1       2018-05-23    2018-05-23    1       789         23678          
     124  100         1       2018-05-23    2018-05-23    0       789         null                             

    now for the second record as i said i must not create another wallet for the same user. so i want to update it with the same wallet number used for the 1st record. Same as it goes with other records with different users

    kind regards

    Tony

    Wednesday, May 23, 2018 6:43 AM
  • User347430248 posted

    Hi TonyR6,

    It is better to create an wallet for a user when user create a new account.

    So when you try to perform any transection, you not need to check each user that he has wallet or not.

    If you are doing batch operations then also it will be helpful because it will decrease some execution time.

    So overall, It will improve the performance.

    Regards

    Deepak

    Wednesday, May 23, 2018 7:05 AM
  • User767034699 posted

    Hi there Deepak,

    thanks for the response,

    that is perfect, actually i will only check if that user exists in the account table, but i will also want to keep track of withdrawn amounts

    kind regards

    Tony

    Wednesday, May 23, 2018 10:30 AM
  • User347430248 posted

    Hi tonyR6,

    I check your table design.

    You had mentioned that,"i will only check if that user exists in the account table, but i will also want to keep track of withdrawn amounts"

    First, Why you want to check for user whether he is exist or not. It is better that you let the user to log in in your system first, then after perform any operation for that specific user.

    As I said before, You can create a wallet when user create a new account. So you need to save the 'Wallet_number' in User's details table. 

    You can easily fetch the wallet number using user_id when you want to perform withdrawal.

    I suggest you to create new table to maintain history of withdrawal.

    So I think you need to modify your table design to make it work properly.

    After that, You can insert a new record for that specific user instead of updating the record.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 24, 2018 5:23 AM