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 — Bahadır Birsöz
– Bahadır Birsöz, Commented Feb 16, 2017 at 10:34

jarlh · Accepted Answer · 2017-02-16 10:33:41Z

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, 2017 at 10:33

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Caspar Over a year ago

What exactly does a case expression do? I'd like to understand it.

jarlh Over a year ago

For different day values, it returns different values. For day value 'MON1', it returns '$p1MON' etc.

Caspar Over a year ago

Thats exactly what I was looking for and it works great. Thanks. I'll accept the answer as soon as it lets me.

Devart Over a year ago

dev.mysql.com/doc/refman/5.7/en/…

Caspar Over a year ago

Hold on - what if I wanted a different column, rather than just P1? Would I require a new query? @jarlh

|

Kickstart · Accepted Answer · 2017-02-16 10:53:53Z

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, 2017 at 10:53

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Comments

Shehary · Accepted Answer · 2017-05-01 03:57:15Z

0

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"

edited May 1, 2017 at 3:57

Shehary

9,99810 gold badges47 silver badges75 bronze badges

answered Feb 16, 2017 at 10:39

Eloy G

11 bronze badge

Collectives™ on Stack Overflow

How do I update multiple rows in one query

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related