Multiple Updates in MySQL

Multiple Updates in MySQL

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Using your example:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

UPDATE multiple rows with different values in one query in MySQL

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

Multiple updates in MySQL for same table one query

Here is how you might construct a case expression to combine into a single update (untested of course)

update rates set
    timestamp = 1643655901, 
    bnb = case base
        when 'btc' then 'value1'
        when 'eth' then 'value4'
        when 'bnb' then 'value7'
        else 'value10' end,
    etc = case base
        when 'btc' then 'value2'
        when 'eth' then 'value5'
        when 'bnb' then 'value8'
        else 'value11' end,
    usdc = case base
        when 'btc' then 'value3'
        when 'eth' then 'value6'
        when 'bnb' then 'value9'
        else 'value12' end
where base in ('btc', 'eth', 'bnb', 'usdc');

MySQL -- Multiple Updates In One Statement?

In your example, they are 5 student ids for only 4 scores to update.

If your list of students to update is not to long, then you can have an update in one query like this :

UPDATE t_student
SET studentScore = CASE
 WHEN studentID=1    THEN 12.76
 WHEN studentID=123  THEN 73.2
 WHEN studentID=33   THEN 83.893
 WHEN studentID=5524 THEN 92.3
 ELSE studentScore END
WHERE studentID IN (1, 123, 33, 5524, 425653)

Nevertheless less you may consider to use a SQL statement including several queries :

UPDATE t_student SET studentScore = 12.76  WHERE studentID = 1;
UPDATE t_student SET studentScore = 73.2   WHERE studentID = 123;
UPDATE t_student SET studentScore = 83.893 WHERE studentID = 33;
UPDATE t_student SET studentScore = 92.3   WHERE studentID = 5524;

---

Related Topics