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, Apr 18 '17 at 15:14
I understand the confusion. For these purposes, just have `r_qty = 2` for all rows. @StefanoZanini — blackandorangecat, Apr 18 '17 at 15:16
`UPDATE POs_Parts SET r_qty = 2, r_author='John' where (f_key_part f_key_po = 6) IN (18, 6), (19, 6), (20, 6), ...` — Giorgos Betsos, Apr 18 '17 at 15:17
If it's the same for each row, you can use the statement you wrote without the `where`. If it's going to be different for each row (without a rule based on other columns values) you will have to execute separate statements — Stefano Zanini, Apr 18 '17 at 15:17

Dave · Accepted Answer · 2017-04-18T15:40:59.033

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)

So I would essentially have to do a `select *` statement before the`update` in order to get the values of the columns I'm not updating? EDIT: nvm, just saw your 2nd edit — blackandorangecat, Apr 18 '17 at 15:41
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. — Dave, Apr 18 '17 at 15:42
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.** — blackandorangecat, Apr 18 '17 at 16:55

MySQL Update with multiple values, and where clauses

1 Answers1