Updating multiple records in one sql statement (MySQL)

If you want to update multiple records, usually you will have to do a loop and generate multiple SQL statement like this

UPDATE table_name SET fieldname = value1 WHERE fieldname = field_id1;
UPDATE table_name SET fieldname = value2 WHERE fieldname = field_id2;
UPDATE table_name SET fieldname = value3 WHERE fieldname = field_id3;

But to save some resource (perhaps), you can also update multiple records in just one single MySQL statement

UPDATE table_name
SET fieldname = CASE value_id
WHEN value_id1 THEN ‘value1’
WHEN value_id2 THEN ‘value2’
WHEN value_id3 THEN ‘value3’
END
WHERE value_id IN (value_id1,value_id2,value_id3)

Leave a Reply

Your email address will not be published. Required fields are marked *