sql - MySQL - UPDATE multiple rows with different values in one query

ID : 20093

viewed : 17

Tags : mysqlsql-updatemysql

Top 5 Answer for sql - MySQL - UPDATE multiple rows with different values in one query

vote vote

94

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.

vote vote

85

MySQL allows a more readable way to combine multiple updates into a single query. This seems to better fit the scenario you describe, is much easier to read, and avoids those difficult-to-untangle multiple conditions.

INSERT INTO table_users (cod_user, date, user_rol, cod_office) VALUES ('622057', '12082014', 'student', '17389551'), ('2913659', '12082014', 'assistant','17389551'), ('6160230', '12082014', 'admin', '17389551') ON DUPLICATE KEY UPDATE  cod_user=VALUES(cod_user), date=VALUES(date) 

This assumes that the user_rol, cod_office combination is a primary key. If only one of these is the primary key, then add the other field to the UPDATE list. If neither of them is a primary key (that seems unlikely) then this approach will always create new records - probably not what is wanted.

However, this approach makes prepared statements easier to build and more concise.

vote vote

70

You can use a CASE statement to handle multiple if/then scenarios:

UPDATE table_to_update  SET  cod_user= CASE WHEN user_rol = 'student' THEN '622057'                    WHEN user_rol = 'assistant' THEN '2913659'                    WHEN user_rol = 'admin' THEN '6160230'                END     ,date = '12082014' WHERE user_rol IN ('student','assistant','admin')   AND cod_office = '17389551'; 
vote vote

66

UPDATE table_name SET cod_user =      CASE      WHEN user_rol = 'student' THEN '622057'     WHEN user_rol = 'assistant' THEN '2913659'     WHEN user_rol = 'admin' THEN '6160230'     END, date = '12082014'  WHERE user_rol IN ('student','assistant','admin') AND cod_office = '17389551'; 
vote vote

55

To Extend on @Trevedhek answer,

In case the update has to be done with non-unique keys, 4 queries will be need

NOTE: This is not transaction-safe

This can be done using a temp table.

Step 1: Create a temp table keys and the columns you want to update

CREATE TEMPORARY TABLE  temp_table_users (     cod_user varchar(50)     , date varchar(50)     , user_rol varchar(50)     ,  cod_office varchar(50) ) ENGINE=MEMORY 

Step 2: Insert the values into the temp table

Step 3: Update the original table

UPDATE table_users t1 JOIN temp_table_users tt1 using(user_rol,cod_office) SET  t1.cod_office = tt1.cod_office t1.date = tt1.date 

Step 4: Drop the temp table

Top 3 video Explaining sql - MySQL - UPDATE multiple rows with different values in one query

Related QUESTION?