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

Question

I'm trying to figure out how to UPDATE multiple rows with different values, and I just don't get it. The solution is everywhere, but for me it's hard to understand.

For example, three updates in 1 query:

UPDATE table_users SET cod_user = '622057' , date = '12082014' WHERE user_rol = 'student' AND cod_office = '123456'; UPDATE table_users SET cod_user = '2913659' , date = '12082014' WHERE user_rol = 'assistant' AND cod_office = '123456'; UPDATE table_users SET cod_user = '6160230' , date = '12082014' WHERE user_rol = 'admin' AND cod_office = '123456'; 

I read an example, but I really don't understand how to make a request. i.e:

 UPDATE table_to_update SET cod_user= IF(cod_office = '123456','622057','2913659','6160230') ,date = IF(cod_office = '123456','12082014') WHERE ?? IN (??) ; 

I don’t quite understand how to execute the request if there are several conditions in the WHERE and in the IF condition: any ideas?

Answer 1

You can do it as follows:

 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 do not understand your date format. Dates should be stored in the database using native date and time types.

Answer 2

MySQL provides a more readable way to combine multiple updates into a single query. This seems to be better suited to the scenario you are describing, much easier to read, and avoids multiple conditions difficult to unravel.

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

It is assumed that the combination user_rol, cod_office is the primary key. If only one of them is PK, add another field to the UPDATE list. If none of them is the primary key (which seems unlikely), then this approach will always create new records - perhaps not what you need.

However, this approach simplifies the collection and simplification of prepared instructions.

Answer 3

You can use the CASE statement to handle multiple if / then scripts:

 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'; 

Answer 4

 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';