Update multiple records with different values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update multiple records with different values

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-16 : 05:43:07

This is a really strange one. Take the following SQL...


declare @tmp TABLE (id int IDENTITY(1,1), username nvarchar(50) NOT NULL, age int NOT NULL)
insert into @tmp (username, age) 
select 'David', 30 union all
select 'Bob', 25 union all
select 'Mikey', 50 union all
select 'Laura', 45 union all
select 'Pedro', 60
select * from @tmp 

update @tmp set age=50 where id=4
update @tmp set age=35 where id=1
update @tmp set age=21 where id=5
select * from @tmp

Is it possible to replace the multiple UPDATE statements with one single statement? For example, if I had the relevant ID row values I need to update, plus the new age values like so...


declare @newAges TABLE (idToUpdate int, age int NOT NULL)
insert into @newAges (idToUpdate, age) 
select 4, 50 union all
select 1, 35 union all
select 5, 21

select * from @newAges

Can the UPDATE be performed by selecting from the second table variable, therefore achieving the update in a single operation?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-16 : 06:05:12

[code]update a
set a.age=b.age
from @tmp a join @newAges b on a.id=b.idToUpdate [/code]