UPDATE Statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

UPDATE Statement

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-03-04 : 01:43:11

I have 3 tables as follow

tab1

ID CurrentUSer
1 XYZ
2 NULL
3 XYZ
4 PQR
5 NULL

tb2

ID IDStatus
1 Close
2 pending
3 approve
4 reject
5 inqueue

tb3

UID Uname Rid
1 ABC 1
2 PQR 2
3 XYZ 3

I want to update tb1.CurrentUSer according to different tb2.IDStatus
with different tb3.Uname

ex- 1. if tb2.IDStatus = pending
then update tb1.CurrentUSer = 'ABC' where tb3.Rid = 1
2. if tb2.IDStatus = reject
then update tb1.CurrentUSer = 'XYZ' where tb3.Rid = 3
3. if tb2.IDStatus = Close
then update tb1.CurrentUSer = NULL

please tell me how i can update tb1.CurrentUSer column for different condition

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:47:17

use a case expression


UPDATE t1
SET t1.CurrentUser=CASE WHEN t2.IDStatus='pending' AND t3.Rid=1
                        THEN 'ABC'
                        WHEN t2.IDStatus='reject' AND t3.Rid=3
                        THEN 'XYZ'
                        WHEN t2.IDStatus='Close' 
                        THEN NULL
                    END
FROM tab1 t1
JOIN tb2 t2
ON t1.ID=t2.ID
JOIN tb3 t3
ON t3.UID=t1.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources