2

I'm curious if I can update all matching fields in a row with a new value at once in MySQL.

I know I can use ALTER TABLE to change default values, but I'm interested only in one row, not in changing the default values of an entire table. I can iterate through each in a loop if necessary, but I'm curious if there is a way to accomplish this more elegantly.

Imagine my row looks like this:

| 1 | Joe | Smith | null | X | X | X | Y | X | Y | Y | X | null | Y | X | null |

Is it possible to update all fields matching X to Y at once?

So the result would look like this:

| 1 | Joe | Smith | null | Y | Y | Y | Y | Y | Y | Y | Y | null | Y | Y | null |

(assume 1 is a primary key, id... if that helps at all)

Can this be done with only one UPDATE query?

Ryan
  • 14,682
  • 32
  • 106
  • 179
  • As far as I'm aware, you'll either need to run a separate `UPDATE` for each (with a `WHERE column = 'X'`), *or* run a stored procedure that iterates through the list of columns (either hardcoded, or from `information_schema`) - which still updates each column individually. – newfurniturey Nov 01 '12 at 04:08
  • That's my experience as well, but I'm hoping a MySQL guru shows me a trick I didn't know about. – Ryan Nov 01 '12 at 04:10
  • Similar question: http://stackoverflow.com/a/3145114/908471 – Anthony Ledesma Nov 01 '12 at 04:26
  • @AnthonyLedesma, perhaps, but that was about changing a whole column, not a row. – Ryan Nov 01 '12 at 04:45
  • @Ryan, the prepared statement can be applied across multiple columns in matching rows. – Anthony Ledesma Nov 01 '12 at 04:52

1 Answers1

5

This will change the value of all columns to 'y' if they are 'x', otherwise leave it unchanged.

update mytable set
col1 = if(col1 = 'x', 'y', col1),
col2 = if(col2 = 'x', 'y', col2),
col3 = if(col3 = 'x', 'y', col3),
...
where id = ?;

Actually, it updates it to the original value otherwise, but semantically that's the same as leaving it unchanged, and in mysql is identical (ie it doesn't count as an "update" in any way)

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Semantically and actually. :) MySQL won't "update" unless the value actually changes. So you'll get the same number of rows affected either way. – cHao Nov 01 '12 at 04:22
  • Thanks. Looks like I'll have to loop (at least as I build the query) after all. – Ryan Nov 01 '12 at 04:46