MySQL - UPDATE multiple rows with different values in one query
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.
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.
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';