Updating multiple MySQL rows with different criteria

Question

I have a MySQL table like so:

purchases

id | item | price
-----------------
 1 |    1 | 12.07
 2 |    3 | 14.24
 3 |    4 | 13.12
 4 |    2 | 11.28

Where id is the primary key, and item is unique (but not a unique key). Let's say I need to adjust about half the values like so:

purchases

id | item | price
-----------------
 1 |    1 | 12.07
 2 |    3 |  3.24 << changed
 3 |    4 | 13.12
 4 |    2 |  5.92 << changed

And say I have all the item/price pairs that need to be updated. I could write my updates like so:

UPDATE purchases SET price = 3.24 WHERE item = 3;
UPDATE purchases SET price = 5.92 WHERE item = 2;

But say this table was a million rows long, and I needed to update half a million rows. I don't want to run half a million update statements. Is there a way to do this in one query given all the item/price pairs?

I've looked at the ON DUPLICATE KEY syntax, but I don't think that'll work given that item isn't a primary key in my table, even though for this query, I want it to act like one.

juergen d · Accepted Answer · 2018-01-23 22:55:32Z

1

You can insert the keys and new values into a temporary table, join against that in your update query. Like this

update your_table 
join temp_table on your_table.item = temp_table.item
set your_table.price = temp_table.new_price

answered Jan 23, 2018 at 22:55

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

2 Comments

user3685285 Over a year ago

I need to do this on a daily basis, so this is not an ideal solution. Cool idea though.

Michael - sqlbot Over a year ago

@user3685285, a temporary table has a specific meaning, and the fact that you don't consider this solution perfectly suitable for daily use suggests that you may not be familiar with temp tables. This is exactly how you do what you are trying to do.

Collectives™ on Stack Overflow

Updating multiple MySQL rows with different criteria

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related