CODEWITHSUNDEEP
mysqlsqlsql-update
geraya6795
geraya6795

Reputation: 3

Multiple updates in MySQL for same table one query

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.

Upvotes: 0

Views: 1071

Answers (1)

Stu
Stu

Reputation: 32599

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');

Upvotes: 2

Related Questions