February 8, 2006 at 8:16 am
I have a table test_update with two columns id and name and I am trying to update the table with the following three update statements. Can I make it all in one update statement with a case statement?
UPDATE test_update
SET name='aaa'
WHERE id=1
UPDATE test_update
SET name='bbb'
WHERE id=2
UPDATE test_update
SET name='ccc'
WHERE id=3
Thanks.
February 8, 2006 at 8:29 am
You can use the following Query :
update test_update
set name=(case when id=1 then 'aaa'
when id=2 then 'bbb'
when id=3 then 'ccc'
else null end)
where id=?
February 8, 2006 at 9:22 am
And change the id = ? to id in (1, 2, 3)
February 8, 2006 at 11:41 am
Thanks for your help. It helped me a lot.
February 9, 2006 at 4:58 am
Alternative:
UPDATE t
set t.Name = u.name
from test_update t
inner join
(
select 1 as Id, 'aaa' as Name union all
select 2, 'bbb' union all
select 3, 'ccc'
)
u on t.Id = u.Id
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply