MySQL Table won't update multiple rows

Question

I am not sure, but I think I have an error in this query:

UPDATE product_discount SET price = '10,15' WHERE key_id = '1,2' 

I can't figure out why, but it only updates number 10 in the column and skips the 15.

Answer 1

I think this is what you need (Assuming Price should be 10 when key_id = 1):

UPDATE product_discount SET price = CASE WHEN key_id = 1 THEN 10 ELSE 15 END
WHERE key_id IN (1,2) 

Answer 2

You could try the method suggested in this answer:

INSERT INTO product_discount (key_id, price) 
VALUES (1, 10), (2, 15)
ON DUPLICATE KEY UPDATE price=VALUES(price);

Be sure to read the linked answer for some caveats, though; for example, this only works if:

• There is a unique key constraint on key_id (e.g., it is the primary key)
• You know that the rows with these ids already exist, or you don't mind inserting new rows if you provide an id that doesn't exist in the table

---

Despite these limitations on its use, the significant advantage of this method over CASE statements is much better readability and parameterization. In Python, for example, here's how you would execute the query given in @Kaf's answer using the MySQLdb module:

query = """UPDATE product_discount SET price = CASE 
    WHEN key_id = %s THEN %s ELSE %s 
    END 
WHERE ID IN (%s, %s);"""

params = (1, 10, 15, 1, 2)

cursor.execute(query, params)

How long is it going to take someone to figure out from that params tuple which values you want updated, and what their values should be? And what if you want to update more than two rows? Not only do you need to rewrite query for every use case of N rows, the params tuple becomes indecipherable garbage if N is anything more than a handful. You could write some helper functions to format both query and params according to the number of updates you need to do, but how long will that take? How easy will it be to understand? How many opportunities for bugs will you introduce in all these extra lines of code?

On the other hand, here's how you would do it using the INSERT ... ON DUPLICATE KEY UPDATE method:

query = """INSERT INTO product_discount (key_id, price) VALUES (%s, %s)
ON DUPLICATE KEY UPDATE price=VALUES(price);"""
params = [(1, 10),
          (2, 15),
          ]
cursor.executemany(query, params)

The key-value relationship is clear and highly readable. If more rows need to be updated, more key-value tuples can be added to the params list. It's not a perfect solution for every scenario, but it's a much better solution than CASE for particular (and I would argue, very common) scenarios.

Answer 3

I think Kaf was close, but there are times where key_id can be 3 or 4 or 5 right? So you need to make sure those values don't change to 15. This should do the trick!

UPDATE product_discount 
SET price = CASE WHEN key_id = 1 THEN 10 
                 WHEN key_id = 2 THEN 15 
                 ELSE price END

Answer 4

I feel what you are trying to do is

update price = 10 when key_id = 1
(OR)
price=15 when key_id = 2.

In that case just do it like

UPDATE product_discount SET price = 
CASE WHEN key_id = 1 THEN 10 
WHEN key_id = 2 THEN 15
ELSE price
END