I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.
For instance, three updates into 1 query:
UPDATE table_users
SET cod_user = '622057'
, date = '12082014'
WHERE user_rol = 'student'
AND cod_office = '17389551';
UPDATE table_users
SET cod_user = '2913659'
, date = '12082014'
WHERE user_rol = 'assistant'
AND cod_office = '17389551';
UPDATE table_users
SET cod_user = '6160230'
, date = '12082014'
WHERE user_rol = 'admin'
AND cod_office = '17389551';
I read an example, but I really don't understand how to make the query. i.e:
UPDATE table_to_update
SET cod_user= IF[cod_office = '17389551','622057','2913659','6160230']
,date = IF[cod_office = '17389551','12082014']
WHERE ?? IN [??] ;
I'm not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?
SuperStormer
4,7495 gold badges20 silver badges32 bronze badges
asked Sep 4, 2014 at 20:48
fran.sand66fran.sand66
10.2k19 gold badges57 silver badges100 bronze badges
1
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.
answered Sep 4, 2014 at 20:53
Gordon LinoffGordon Linoff
1.2m53 gold badges596 silver badges739 bronze badges
15
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.
John
11.9k11 gold badges90 silver badges156 bronze badges
answered Jan 19, 2016 at 0:13
18
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';
answered Sep 4, 2014 at 20:54
Hart COHart CO
33.3k5 gold badges44 silver badges60 bronze badges
1
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';
Saghachi
7199 silver badges16 bronze badges
answered Dec 11, 2016 at 7:43
Akshay BhanAkshay Bhan
1952 silver badges12 bronze badges
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
answered Mar 30, 2020 at 11:45
SabSab
4705 silver badges16 bronze badges
In php, you use multi_query
method of mysqli instance.
$sql = "SELECT COUNT[*] AS _num FROM test;
INSERT INTO test[id] VALUES [1];
SELECT COUNT[*] AS _num FROM test; ";
$mysqli->multi_query[$sql];
comparing result to transaction, insert, case methods in update 30,000 raw.
Transaction: 5.5194580554962
Insert: 0.20669293403625
Case: 16.474853992462
Multi: 0.0412278175354
As you can see, multiple statements query is more efficient than the highest answer.
Just in case if you get error message like this:
PHP Warning: Error while sending SET_OPTION packet
You may need to increase the max_allowed_packet
in mysql config file.
answered Dec 19, 2021 at 7:50
SaghachiSaghachi
7199 silver badges16 bronze badges
UPDATE Table1 SET col1= col2 FROM [SELECT col2, col3 FROM Table2] as newTbl WHERE col4= col3
Here col4 & col1 are in Table1. col2 & col3 are in Table2
I Am trying
to update each col1 where col4 = col3 different value for each row
answered Apr 23, 2020 at 4:25
ankit giriankit giri
3083 silver badges9 bronze badges
I did it this way:
UPDATE push_setting SET status = #{setting.status}
WHERE type = #{setting.type} AND user_id = #{userId};
where PushSettings is
public class PushSettings {
private List settings;
private String userId;
}
it works fine
answered Apr 16, 2020 at 13:30
ru51anru51an
1151 gold badge1 silver badge10 bronze badges
1