MySQL Update with multiple values, and where clauses

Question

I'm stumped. I have read a lot of different questions similar to this, but still can't figure things out.

Here is a snippet of the before table. (POs_Parts)

+-------+------------+----------+---------+-------+----------+
| p_Key | f_key_part | f_key_po | partQty | r_qty | r_author |
+-------+------------+----------+---------+-------+----------+
|     0 |         18 |        6 |       2 |     0 | NULL     |
|     1 |         19 |        6 |       3 |     0 | NULL     |
|     2 |         20 |        6 |       1 |     0 | NULL     |
|     3 |         18 |        8 |       1 |     0 | NULL     |
+-------+------------+----------+---------+-------+----------+

Here is how I would like it to be after the update statements. (The last two columns have been updated)

+-------+------------+----------+---------+-------+----------+
| p_Key | f_key_part | f_key_po | partQty | r_qty | r_author |
+-------+------------+----------+---------+-------+----------+
|     0 |         18 |        6 |       2 |     2 | John     |
|     1 |         19 |        6 |       3 |     2 | John     |
|     2 |         20 |        6 |       1 |     0 | John     |
|     3 |         18 |        8 |       1 |     1 | John     |
+-------+------------+----------+---------+-------+----------+

I think that this statement does what I want - for one row - but I have tons of rows that need to be changed at a time, so I'd like to avoid executing a lot of statements one at a time.

UPDATE POs_Parts SET r_qty = 2, r_author='John' where f_key_part = 18 and f_key_po = 6;

Here is the question I was trying to use as a reference. Multiple Updates in MySQL

How can I rewrite this without having to run a bunch of Update statements? I will be using PHP to create the query. Any suggestions would be appreciated.

What determines the value of field r_qty after the UPDATE? — Giorgos Betsos
– Giorgos Betsos, Commented Apr 18, 2017 at 15:14
I understand the confusion. For these purposes, just have r_qty = 2 for all rows. @StefanoZanini — blackandorangecat
– blackandorangecat, Commented Apr 18, 2017 at 15:16

Dave · Accepted Answer · 2017-04-18 15:40:59Z

3

If you follow the reference you linked, the SQL would be something like

INSERT INTO
    POs_Parts (p_Key, f_key_part, f_key_po, partQty, r_qty, r_author)
VALUES
    (0, 18, 6, 2, 2, 'John'),
    (1, 19, 6, 3, 2, 'John'),
    (2, 20, 6, 1, 0, 'John'),
    (3, 18, 8, 1, 1, 'John')
ON DUPLICATE KEY UPDATE
    f_key_part = VALUES(f_key_part),
    f_key_po = VALUES(f_key_po),
    partQty = VALUES(partQty),
    r_qty = VALUES(r_qty),
    r_author = VALUES(r_author)

(You might be able to omit the unchanged columns--I haven't tested this or ever used such syntax before. ~~Edit: you cannot omit the columns~~ Edit 2: You can omit the columns. See the updated syntax below.)

I think you'll find "a bunch of SQL statements" is the much more straightforward route.

Here's my test:

MariaDB [test]> select * from POs_Parts;
+-------+------------+----------+---------+-------+----------+
| p_Key | f_key_part | f_key_po | partQty | r_qty | r_author |
+-------+------------+----------+---------+-------+----------+
|     0 |         18 |        6 |       2 |     0 | NULL     |
|     1 |         19 |        6 |       3 |     0 | NULL     |
|     2 |         20 |        6 |       1 |     0 | NULL     |
|     3 |         18 |        8 |       1 |     0 | NULL     |
+-------+------------+----------+---------+-------+----------+
4 rows in set (0.00 sec)

MariaDB [test]> INSERT INTO POs_Parts (p_Key, f_key_part, f_key_po, partQty, r_qty, r_author) VALUES (0, 18, 6, 2, 2, 'John'), (1, 19, 6, 3, 2, 'John'), (2, 20, 6, 1, 0, 'John'), (3, 18, 8, 1, 1, 'John') ON DUPLICATE KEY UPDATE     f_key_part = VALUES(f_key_part), f_key_po = VALUES(f_key_po), partQty = VALUES(partQty), r_qty = VALUES(r_qty), r_author = VALUES(r_author);
Query OK, 8 rows affected (0.00 sec)
Records: 4  Duplicates: 4  Warnings: 0

MariaDB [test]> select * from POs_Parts;
+-------+------------+----------+---------+-------+----------+
| p_Key | f_key_part | f_key_po | partQty | r_qty | r_author |
+-------+------------+----------+---------+-------+----------+
|     0 |         18 |        6 |       2 |     2 | John     |
|     1 |         19 |        6 |       3 |     2 | John     |
|     2 |         20 |        6 |       1 |     0 | John     |
|     3 |         18 |        8 |       1 |     1 | John     |
+-------+------------+----------+---------+-------+----------+
4 rows in set (0.00 sec)

Shorter syntax:

INSERT INTO
    POs_Parts (p_Key, r_qty, r_author)
VALUES
    (0, 2, 'John'),
    (1, 2, 'John'),
    (2, 0, 'John'),
    (3, 1, 'John')
ON DUPLICATE KEY UPDATE
    r_qty = VALUES(r_qty),
    r_author = VALUES(r_author)

edited Apr 18, 2017 at 15:40

answered Apr 18, 2017 at 15:19

Dave

1,9951 gold badge16 silver badges29 bronze badges

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

3 Comments

blackandorangecat Over a year ago

So I would essentially have to do a select * statement before theupdate in order to get the values of the columns I'm not updating? EDIT: nvm, just saw your 2nd edit

Dave Over a year ago

I retried and found you can omit the columns you're not updating. I've updated the answer. See if it works for you now.

blackandorangecat Over a year ago

Thanks a million. Because I had to have f_key_part and f_key_po match certain variables, I had to split it into two queries. One that found the p_key where that criteria (f_key_part etc.) was met, and then use the code that you gave me to take that p_key and other variables and insert them correctly. tldr; It was hard and I really appreciate your help.

Collectives™ on Stack Overflow

MySQL Update with multiple values, and where clauses

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related