CODEWITHSUNDEEP
mysqlunion
user63371
user63371

Reputation: 539

MySql UNION for UPDATE

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.

Upvotes: 4

Views: 16058

Answers (3)

Kristen
Kristen

Reputation: 4301

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

Update 10/28/2014, converted to work for MySQL:

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

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425763

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

Upvotes: 7

notnot
notnot

Reputation: 4642

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.

Upvotes: 0

Related Questions