Multiple updates in MySQL for same table one query

Question

Is there a way to update multiple rows at once in single query in MySQL? Right now i have 4 queries for each condition. I want to combine all the following updates into one query with the best performance.

UPDATE rates SET timestamp = 1643655901, bnb = 'value1', eth = 'value2', usdc = 'value3' WHERE base = 'btc'; UPDATE rates SET timestamp = 1643655901, bnb = 'value4', btc = 'value5', usdc = 'value6' WHERE base = 'eth'; UPDATE rates SET timestamp = 1643655901, eth = 'value7', btc = 'value8', usdc = 'value9' WHERE base = 'bnb'; UPDATE rates SET timestamp = 1643655901, bnb = 'value10', btc = 'value11', eth = 'value12' WHERE base = 'usdc';

timestamp field can be same for any query

value1 - value12 dynamic data based on different variables

I was looking into other answers on a similar question but have not been able to build the query, thanks for any help.

You could probably concoct a case expression but what you have is a lot easier to read and amend. — Stu
– Stu, Commented Jan 31, 2022 at 19:50
I'm not worried about reading, I would like to do it in one query, I've looked at CASE examples but did not find a similar case for me. — geraya6795
– geraya6795, Commented Jan 31, 2022 at 19:58
Please provide SHOW CREATE TABLE rates; then I may have a way using IODKU to achieve your goal. — Rick James
– Rick James, Commented Feb 1, 2022 at 1:52

Stu · Accepted Answer · 2022-01-31 20:04:53Z

Here is how you might construct a case expression to combine into a single update (untested of course)

update rates set timestamp = 1643655901, bnb = case base when 'btc' then 'value1' when 'eth' then 'value4' when 'bnb' then 'value7' else 'value10' end, etc = case base when 'btc' then 'value2' when 'eth' then 'value5' when 'bnb' then 'value8' else 'value11' end, usdc = case base when 'btc' then 'value3' when 'eth' then 'value6' when 'bnb' then 'value9' else 'value12' end where base in ('btc', 'eth', 'bnb', 'usdc');

Collectives™ on Stack Overflow

Multiple updates in MySQL for same table one query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related