Update a single column on multiple rows with one SQL query

I need to update a single column over a thousand rows in the database. Normally when I need to do this, I’ll do the following:

UPDATE [table] SET DATA="FOO" WHERE ID=23;
UPDATE [table] SET DATA="ASD" WHERE ID=47;
UPDATE [table] SET DATA="FGH" WHERE ID=83;
UPDATE [table] SET DATA="JKL" WHERE ID=88;
UPDATE [table] SET DATA="QWE" WHERE ID=92;
UPDATE [table] SET DATA="BAR" WHERE ID=97;

I feel like there should be a way to do this easily, but after searching around online, I cannot find a solution.

What I was hoping for was this:

-- THIS DOES NOT WORK
UPDATE [table]
  SET DATA=("FOO", "ASD", "FGH", "JKL", "QWE", "BAR") 
  WHERE ID=(23, 47, 83, 88, 92, 9);

An important piece of information to note is that I am always setting one column, the same column across a specific set of rows.

Does anyone know the best way to do this?

Answer

You can actually do it using insert into …on duplicate key update

insert into [table](ID,DATA) 
values(23,'FOO'),(47,'ASD'),(54,'DSF')..,
on duplicate key update DATA=values(DATA)

Advertisement

Answer