Single Statement to do an update and insert?

[kosmokramer]

Hey. I am trying to keep track of a person's balance. To do this I have two tables, one that keeps the person's balance, and another which has all the transactions and descriptions. So, when the person submits a new transaction, I want both tables to be updated. I can get the insert to work, but am unsure how to do the nesting on the update statement. I want to be able to combine them into one string so I can use it on an asp page. So, basically what I am asking for is how to get the update statement working, and when it is, how to combine the two statements into one string.

The two statements are:

Insert Into my_Transactions (credit_Debit, transactionDesc, membName,password_Field)
Values (30.00,'Transaction description','Jon Doe','somePassword')

and

Update members_Table set Balance = (Select sum(credit_Debit) from my_Transactions where membName='Jon Doe' and password_Field='somePassword')

Thanks,
Paul

 

[Steve101]

The following should work as the Update statement, and be far more efficient:

Update members_Table
set Balance = Balance + 30
from my_Transactions
where membName='Jon Doe' and password_Field='somePassword'

If you pair the two statements (ie. the insert and updates), and they always happen together, then they should stay in sync. In this case, the $30, would of course be a variable which would apply equally to both queries.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

The "Combination" is generally achieved via the "Transaction" method. Help is Helpful, using the KeyWord(s).

[Begin Transaction, several SQL statements & End Transaction]

MichaelRed
m.red@att.net

Searching for employment in all the wrong places