Multiple UPDATE, same column, different values, different WHERE conditions

I’ve found plenty of info around about updating multiple rows with the same value using “WHERE columname IN”, and I’ve got that down. But, I’m needing to UPDATE a column in multiple rows with a different value for each WHERE condition.

My updates are being done as individual queries like this:

UPDATE tablename SET widget='zamu-xxx' WHERE widget='zamu';
UPDATE tablename SET widget='flabu-yyy' WHERE widget='flabu';

Is there any way to do something like this in a single query?

Thanks.

Put all the old/new value pairs in a table. Then use the multi-table update syntax from the manual to do it with one query.

http://dev.mysql.com/doc/refman/5.0/en/update.html

nice one, dan – effective, efficient, and explained with brevity and clarity

if it’s only a few values, though, i would simply hardcode the updates

:slight_smile:

Thanks for the lead. The most number of rows I anticipate needing to update at a time is maybe five, so just one at a time might end up being what I go with. But, I can see needing to do this for a much greater number at a time, so I’m going to try to wrap my head around the multi-table method.