Is it possible to update multiple records in a table with different values?

user_16608114 0
Viewed 40

I am working with SQL and I need to do several Updates with the following:

UPDATE databaseID SET count = 51 WHERE codCountry = "ES" AND file = "ES_IN.txt"
UPDATE databaseID SET count = 15 WHERE codCountry = "ES" AND file = "ES_DA.txt"
UPDATE databaseID SET count = 84 WHERE codCountry = "CO" AND file = "CO_SE.txt"
UPDATE databaseID SET count = 74 WHERE codCountry = "CO" AND file = "CO_TE.txt"

My question is, is it possible to do something like the following, that is, update multiple rows of the table in a single query?

UPDATE databaseID (SET count = 51 WHERE codCountry = "ES" AND file = "ES_IN.txt"), (SET count = 15 WHERE codCountry = "ES" AND file = "ES_DA.txt")...
case sql-update mysql sql
2 Answers

I would suggest a derived table and join:

UPDATE databaseID d JOIN
       (SELECT 'ES' as codCountry, 'ES_IN.txt' as file, 15 as count UNION ALL
        SELECT 'CO' as codCountry, 'CO_SE.txt' as file, 51 as count UNION ALL
         . . .
       ) x
       USING (codCountry, file)        
    SET d.count = x.count;

This is even simpler if the original data is already in a table. Then you can just use that table.

user_11440350

Use a CASE expression:

UPDATE databaseID 
SET count = CASE 
    WHEN codCountry = 'ES' AND file = 'ES_IN.txt' THEN 51 
    WHEN codCountry = 'ES' AND file = 'ES_DA.txt' THEN 15
    WHEN codCountry = 'CO' AND file = 'CO_SE.txt' THEN 84
    WHEN codCountry = 'CO' AND file = 'CO_TE.txt' THEN 74
    ELSE count
END

MySql will not actually update the row if the ELSE part is reached.

Or you can add a WHERE clause:

UPDATE databaseID 
SET count = CASE 
    WHEN codCountry = 'ES' AND file = 'ES_IN.txt' THEN 51 
    WHEN codCountry = 'ES' AND file = 'ES_DA.txt' THEN 15
    WHEN codCountry = 'CO' AND file = 'CO_SE.txt' THEN 84
    WHEN codCountry = 'CO' AND file = 'CO_TE.txt' THEN 74
END
WHERE (codCountry, file) IN 
  ('ES', 'ES_IN.txt'), ('ES', 'ES_DA.txt'), ('CO', 'CO_SE.txt'), ('CO', 'CO_TE.txt')

and remove the ELSE part of the CASE expression.

user_104988280
Related