Updating multiple rows in a single statement
Posted on 2006-11-16
I thought I'd have a look at trying to make our update queries more efficient, because our code currently generates multiple update queries when I'd rather just operate on multiple rows at the same time.
I've tried both of the revised queries below, because (to me), they seemed to be the most logical. Neither of them work, however. Immediately below is the syntax which is currently used and requires three seperate queries to complete all the updates.
UPDATE ord_products SET modelcode='noo1' WHERE (quoteID='foo1');
UPDATE ord_products SET modelcode='noo2' WHERE (quoteID='foo2');
UPDATE ord_products SET modelcode='noo3' WHERE (quoteID='foo3');
UPDATE ord_products SET (modelcode='noo1' WHERE quoteID='foo1'),(modelcode='noo2' WHERE quoteID='foo2'),(modelcode='noo3' WHERE quoteID='foo3');
UPDATE ord_products (modelcode) VALUES('noo1'),('noo2'),('noo3') WHERE (quoteID IN ('foo1','foo2','foo3'));
Can anyone suggest a solution for this to run this in one query?
Thanks in advance.