Union two update statements in mysql to get count of affected row for both

Question

In MySQL, how can I use use union with two update statements, or run two update statements simultaneously, so I get the count of affected rows of both (together)?

I'm trying to run the two of them together, instead of having two separate ones. The two of them update two different rows, so they cannot be run together in a single statement. Any help?

Example:

update prodb set buyer = buyer+1 where userId = 1
 union
update prodb set seller = seller+1 where userId = 2;

Update: Did it this way, but it still returns affected rows as one. I guess it's returning the number of rows from the second statement only.

$stmt = '
   update prodb set buyer = buyer+1 where userId = 1;
   update prodb set seller = seller+1 where userId = 2;
';

$update = $dbc->prepare($stmt);

$update->execute();

The Onin · Accepted Answer · 2014-10-02T14:37:11.473

2

Don't use union for update. Use multi_query (in case you are running php).

$db_link->multi_query("update prodb set buyer = buyer+1 where userId = 1; update prodb set seller = seller+1 where userId = 2;");

Never forget to add semicolon at last sql statement.

edited Oct 02 '14 at 14:37

answered Oct 02 '14 at 14:29

The Onin

5,068
2
38
55

Is this possible when using PHP's PDO? – jmenezes Oct 02 '14 at 14:33
It seems so. [link](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – The Onin Oct 02 '14 at 14:36
I got it working, but funny thing is, It still returns 1 as affected rows, even when the number of affected rows is two. – jmenezes Oct 02 '14 at 14:59
If you are using multi_query, you need to advance the cursor to get next result, it's just how multi_query works. – The Onin Oct 02 '14 at 15:11

Union two update statements in mysql to get count of affected row for both

1 Answers1