CODEWITHSUNDEEP
mysqlsql
flyingfox
flyingfox

Reputation: 13506

Can we merge multiple update sql into one sql?

I have a list of update sql as below:

UPDATE variable SET type='aa1' WHERE type='aaa';
UPDATE variable SET type='x1' WHERE type='bbb';
UPDATE variable SET type='da1' WHERE type='ccc';
UPDATE variable SET type='ac1' WHERE type='ddd';
-- more update sql

Since there are many sqls but they looks same,I am wondering can we merge it into one sql such as below:

UPDATE variable SET type=? WHERE type=?;

I know we can use CASE WHEN,but the solution seems ugly,is there more elegant way to do it?(Maybe key value mapping?)

   -- I do not want to do it like this
    UPDATE variable
     SET type=
      (
        CASE WHEN type='aa1' THEN 'aaa'
        CASE WHEN type='x1' THEN 'bbb'
       END
      )
     WHERE type IN('aa1','x1');

Also I know we can use some program language such as jdbc to do it,but I want to find if there is a pure sql solution

Thanks in advance!

Upvotes: 1

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can use a JOIN:

UPDATE variable v JOIN
       (SELECT 'aa1' as newtype, 'aaa' as oldtype UNION ALL
        SELECT 'x1', 'bbb' UNION ALL
        SELECT 'da1', 'ccc' UNION ALL
        SELECT 'ac1', 'ddd'
       ) v2
       ON v2.oldtype = v.type
    SET v.type = v2.newtype;

Upvotes: 1

Related Questions