Is there way to update multiple rows in the same table, with different values?

Question

Using Microsoft SQL 2014, is there way to update multiple rows in the same table, with different values?

I have a table of costs for components and periodically I need to change the costs for all the individual components.

Currently, I do the following :

UPDATE table1 SET cost = '250' WHERE component = 'bicycle'
UPDATE table1 SET cost = '90' WHERE component = 'chainsaw'
UPDATE table1 SET cost = '0.10' WHERE component = 'overripe banana'

etc.

This seems a little inefficient to my mind, so is there a better way to do this? Something like an INSERT INTO table1 VALUES () but for an UPDATE statement.

So instead of running 10000 UPDATE statements, I can run one that updates multiple different rows.

Maybe it's not inefficient, which is fine too.

Thanks

What did you do to end up with those nasty so-called "smart" quotes in the query? They are wrong for SQL. — Joel Coehoorn
– Joel Coehoorn, Commented Oct 7, 2020 at 13:10

Joel Coehoorn · Accepted Answer · 2020-10-07 13:26:35Z

2

You can still use a table-value constructor (just like with an INSERT) and a JOIN to create your update:

UPDATE old
SET old.cost = new.cost
FROM table1 old  
INNER JOIN (VALUES 
        ('bicycle', 250.00), 
        ('Chainsaw', 90.00), 
        ('overripe banana', 0.10)
    ) AS new(Component,Cost) ON new.Component = old.Component

edited Oct 7, 2020 at 13:26

answered Oct 7, 2020 at 13:15

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

Comments

mjade · Accepted Answer · 2022-10-30 02:53:45Z

0

i also was looking for the solution on this and i cant find any.. i later on comeup with this solution incase someone is also having trouble with this..

UPDATE table1 
SET cost = CASE component 
WHEN 'bicycle' THEN '250' 
WHEN 'chainsaw' THEN '90' 
WHEN 'overripe banana' THEN '0.10' 
ELSE cost 
END
WHERE component IN ('bicycle', 'chainsaw', 'overripe banana');

edited Oct 30, 2022 at 2:53

answered Oct 30, 2022 at 2:38

mjade

6491 gold badge5 silver badges10 bronze badges

Collectives™ on Stack Overflow

Is there way to update multiple rows in the same table, with different values?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related