Complex Update Statement using Case

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 2000 Forums

SQL Server Development (2000)

Complex Update Statement using Case

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2003-09-11 : 07:46:28

hi ,

i have following statements
UPDATE TABLE1
SET Status = 'INACTIVE'
WHERE COL1 IN
(SELECT COL1
from #TABLE2
WHERE Colint > ValueX)

UPDATE TABLE1
SET Status = 'ACTIVE'
WHERE COL1 IN
(SELECT COL1
from TABLE2
WHERE Colint <= ValueX)

I WANNA ACHIEVE THE SAME RESULT using CASE stmt within single update stmt.
help appreciated.

tx n regards,
praveen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-11 : 07:49:47

UPDATE T1
SET Status=CASE WHEN T2.ColInt>ValueX THEN 'INACTIVE' ELSE 'ACTIVE' END
FROM Table1 T1 INNER JOIN #Table2 T2 ON T1.COL1=T2.COL1

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-11 : 07:53:14

UPDATE table1
set status = case when colint > valuex then 'inactive' else 'active' end
FROM table1
INNER JOIN
table2
on table1.col1 = table2.col1

Try that. You may find performance is better simply doing the two separate queries.

Edited to add, I just noticed that in your examples one looks at #table2 and the other at table2. Is this correct or a typo? If it's correct then the query becomes more involved.

-------
Moo. :)

peddi_praveen
Starting Member

48 Posts

Posted - 2003-09-11 : 10:13:59

hi guys,
tx for quicky.. its a typo.... ,i vl get bk to u once, i finish testing this....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources