How do I update multiple rows in one query

Question

I know this question may be asked too many times on these forums but I genuinely cannot find a solid answer. They all give different methods.

Anyway, I have this code to update one row:

$sql = "UPDATE $userName SET P1 = '$p1MON' WHERE day = 'MON1'";

Is it possible to update multiple rows in the same query? Something like this:

$sql = "UPDATE $userName SET P1 = '$p1MON' WHERE day = 'MON1',
        UPDATE $userName SET P1 = '$p1TUE' WHERE day = 'TUE1',
        UPDATE $userName SET P1 = '$p1WED' WHERE day = 'WED1'";

Or, to update multiple rows in one query, do they have to share an identifier?

Thanks.

EDIT: A suggestion in the comments , doesn't seem to work...

$sql = "UPDATE $userName SET P1 = '$p1MON' WHERE day = 'MON1';
        UPDATE $userName SET P1 = '$p1TUE' WHERE day = 'TUE1';
        UPDATE $userName SET P1 = '$p1WED' WHERE day = 'WED1'";

Duplicate question, [http://stackoverflow.com/questions/3432/multiple-updates-in-mysql](http://stackoverflow.com/questions/3432/multiple-updates-in-mysql) — Bahadır Birsöz, Feb 16 '17 at 10:34

score 10 · Answer 1 · answered Feb 16 '17 at 10:33

10

Use a case expression:

UPDATE $userName
SET P1 = case day
         when 'MON1' then '$p1MON'
         when 'TUE1' then '$p1TUE'
         when 'WED1' then '$p1WED'
         end
where day IN ('MON1', 'TUE1', 'WED1')

answered Feb 16 '17 at 10:33

jarlh

42,561
8
45
63

What exactly does a case expression do? I'd like to understand it. – Caspar Feb 16 '17 at 10:35
For different day values, it returns different values. For day value 'MON1', it returns '$p1MON' etc. – jarlh Feb 16 '17 at 10:36
Thats exactly what I was looking for and it works great. Thanks. I'll accept the answer as soon as it lets me. – Caspar Feb 16 '17 at 10:37
https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case – Devart Feb 16 '17 at 10:37
Hold on - what if I wanted a different column, rather than just P1? Would I require a new query? @jarlh – Caspar Feb 16 '17 at 10:39
It depends on the situation. The WHERE clause decides which rows to update. If you want to update the same rows as before, you can simply add another column update to the statement. If you want other rows to be updated, I'd probably consider a separate UPDATE statement. (Depends on the table size, number of updated rows etc.) – jarlh Feb 16 '17 at 10:41

score 0 · Answer 2 · edited May 01 '17 at 03:57

Depending you database engine you can try something like this

$sql = "UPDATE $userName SET P1 = '$p1MON' WHERE day = 'MON1'; UPDATE $userName SET P1 = '$p1TUE' WHERE day = 'TUE1'; UPDATE $userName SET P1 = '$p1WED' WHERE day = 'WED1'"

Or

$sql = "UPDATE $userName SET P1 = '$p1MON' WHERE day = 'MON1' go  UPDATE $userName SET P1 = '$p1TUE' WHERE day = 'TUE1' go  UPDATE $userName SET P1 = '$p1WED' WHERE day = 'WED1'"

Using ; or GO (depending of you database) separate instructions to execute then on "one query"

score 0 · Answer 3 · answered Feb 16 '17 at 10:53

0

is the field day a unique key field?

If so you can use on duplicate key update syntax.

INSERT INTO $userName(day, P1) VALUES
('MON1', '$p1MON'),
('TUE1', '$p1TUE'),
('WED1', '$p1WED')
ON DUPLICATE KEY UPDATE P1=VALUES(P1)

answered Feb 16 '17 at 10:53

Kickstart

21,403
2
21
33

How do I update multiple rows in one query

3 Answers3