• Welcome, Guest. Please login.
 
May 07, 2021, 11:50:48 PM

News:

Welcome to the SQLitening support forums!


A beginner question concerning updating a field

Started by Gafny Jacob, April 25, 2009, 01:06:10 PM

Previous topic - Next topic

Gafny Jacob

I have a columne named "ammount" that containes a certain value. I need to add 100 to this value.
How can I prevent a hipothetical situation where two people update the same field at the same time.

Bern Ertl

If you are performing the addition within the SQL statement, you are not going to be able to prevent a double update skewing the data.

If you perform the addition in your app and UPDATE with a fixed value (being 100 more than the value last read by your app), then the value will always be stored as the user expects.

Alternatively, you can leave it to the end user to refresh his view of the data and verify that all is as expected.

Gafny Jacob

Assume I have a table "Users" and one of the columns name "Debit" and "UserId"
I have another table "Transaction" width column "amount" and "UserId"
Every time I add a row to transaction table, I have to read the appropriate user from "Users" table,  and add the value of the "amount" column to the "debit" column, can you show me please the sql statement how I do This (Only the part that update the "users" table
Thanks


Paul Squires

Try processing both SQL statements in the one transaction.

(not sure if your UserID is numeric or alphanumeric. In the code below I assumed it was alphanumeric)


INSERT INTO Transaction (amount, userid)
VALUES (19.95, 'ABC123');
UPDATE users
SET Debit = Debit + 19.95,
WHERE UserId = 'ABC123';


This would be wrapped in a transaction so both tables would be updated prior to SQLite releasing the lock on the database.




Bern Ertl

You can also create a TRIGGER so that the UPDATE takes place automatically when you do the INSERT:

slExe "CREATE TRIGGER fku_update_users_after_transaction_insert AFTER INSERT ON Transaction FOR EACH ROW
  BEGIN
    UPDATE users SET Debit = Debit + NEW.amount WHERE NEW.UserId = users.UserId;
  END;"