how to update two rows in a single table with one command in my sql

Question

I have a table

 accountdetails

with fields

customerid,balance,account id

Now Im trying to update this through my node app where when I transfer amount, in one record the value should be debited and in another it should be credited

var data = {customerid,transferamount,accountid};


 con.query('update accountdetails set balance = balance-? WHERE customerid = ? ,
 [data.transferamount,data.customerid]')

con.query('update  accountdetails set balance = balance+? WHERE accountid = ?,
[data.transferamount,data.accountid]')

So currently I have two commands to do this how can we limit this to one command since both are basically updating single table.

Any idea

I don't think you should. Two queries are fine since the where clause is entirely different. — kawadhiya21, Jul 21 '17 at 12:34
To reduce the number of rest calls used I want to make it to single command — user7350714, Jul 21 '17 at 12:36
Take a look at this post: https://stackoverflow.com/questions/23266854/node-mysql-multiple-statement-in-one-query — Bla..., Jul 21 '17 at 12:37
dude atomicity will take a toss. Update one by one. Imagine multiple operations on the row with additions and subtractions. — kawadhiya21, Jul 21 '17 at 12:39

score 0 · Answer 1 · answered Jul 21 '17 at 12:44

Agree with @kawadhiya21, maybe better don't do it... you have to benchmark both cases to figure out performance, but in general, it is possible:

sql = 'update accountdetails';
sql += 'set balance = if(customerid = ?, balance - ?, if(accountid = ?, balance + ?, balance) ) ';
sql += 'where customerid = ? or accountid = ?';
con.query(sql, [
    data.customerid,
    data.transferamount,
    data.accountid,
    data.transferamount,
    data.customerid,
    data.accountid
]);

score 0 · Answer 2 · edited Jul 21 '17 at 14:25

0

Good day,

Yes these two commands must remain to update your data, since you have different conditions on your commands.

Your first command condition is WHERE customerid, while the other command condition is WHERE accountid, so it is not possible to combine these into one single command.

edited Jul 21 '17 at 14:25

Tamás Sengel

55,884
29
169
223

answered Jul 21 '17 at 12:45

Victor Vidamo

1
1

Nick Loudaros · Answer 3 · 2017-07-21T12:55:42.360

One of the best practices is utilising mysql's Triggers. These are programmable database objects that do the job. What you basically describe is an AFTER UPDATE trigger. For Example:

CREATE TRIGGER doTheJob AFTER UPDATE ON accountdetails
FOR EACH ROW

BEGIN
UPDATE accountdetails
SET balance = +? WHERE accountid = ?

  END;
  DELIMITER ;

More info specifically on AFTER UPDATE triggers here More info on triggers here

score 0 · Answer 4 · answered Jul 21 '17 at 12:56

Another way is to use stored procedures to accomplish this: https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html

With a stored procedure you can add 3 parameters (customerid, transferamount, accountid) and hide all the internal details of the SQL calls (for instance the solution from Vladimir Kovpak)

how to update two rows in a single table with one command in my sql

4 Answers4