MySql UNION for UPDATE

Question

Is there a way to update multiple rows with different values for each row using a single SQL query? I have to update one colum in many rows with different data. Using individual update queries for each row seems excessive so if it's possible I would like to consolidate this process into a single SQL statement or at least reduce the number of queries required.

I am using PHP with the Zend framework and MySql.

Answer 1

Create a temporary table and fill it with:

CREATE TEMPORARY TABLE temptable (id INTEGER, VALUE VARCHAR(200))

INSERT
INTO temptable
VALUES
  ('1', 'val1'),
  ('2', 'val2'),
  ('3', 'val3'),
  ('4', 'val4')

Then issue:

UPDATE
  mytable m, temptable t
SET m.value = t.value
WHERE m.id = t.id

Answer 2

I know this works for SQL Server, so it's worth a try in MySQL.

update xtable
set a = 
   Case
     when a = "a"
     then z
     when a = "b"
     then y
   End
where ...

You can construct the case statement based on your different rows.

Answer 3

Don't know about MySQL specifically, but to update multiple rows based on a SELECT, or a UNION of multiple SELECTs, I would do

UPDATE U
SET MyColumn = T.OtherColumn
FROM MyUpdateTable AS U
     JOIN
     (
         SELECT [OtherColumn] = OtherColumn1
         FROM MyOtherTable1
         WHERE ...
         UNION
         SELECT OtherColumn2
         FROM MyOtherTable2
         WHERE ...
     ) AS T
         ON T.ID = U.ID