How to do 2 update statements at a time?

update accounts se开发者_Python百科t type=1 where id=2;

update accounts set type=3 where id=4;

At the same time? If you mean that you want to make sure no other queries affect the table before your queries are done, you're going to want to wrap them in a transaction.

exactly what you ask will be

update accounts set type = case id when 2 then 1 else 3 end where id in (2,4)

but in general this is of course impossibe.

This:

UPDATE ACCOUNTS t SET t.type = 1 WHERE t.id = 2; UPDATE ACCOUNTS t SET t.type = 3 WHERE t.id = 4;

...is acceptable SQL statement syntax - the ";" is a statement delimiter. The statements won't be executed simultaneously - nothing in a database is, everything is queued & processed in order of submission (though fiddling with isolation levels can work around that).

However, most APIs & IDEs prevent multiple statement execution like this in order to stop SQL injection attacks. What are you using that you find is stopping you from executing the statement?

If you want them to occur at once you could use a transaction:

SET autocommit=0;
START TRANSACTION;
update accounts set type=1 where id=2;
update accounts set type=3 where id=4;
COMMIT;

Note that you need to disable auto commit or the commit will occur when the first update occurs.

You may find this article interesting. I got more out of the comments than the article, but the article was interesting: http://blog.bubble.ro/how-to-make-multiple-updates-using-a-single-query-in-mysql/

His technique probably won't work for you, but it is one approach.

The best bet would be to write a stored procedure and have it do the multiple updates, or, if there is some way for a program to determine the values for the second query, you could use a trigger, but I expect that won't help you.

It's not a generic solution but in this special case:

update accounts
   set type = id - 1
 where id = 2
    or id = 4;

That's one statement...