Choose column to update - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Choose column to update

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2008-04-16 : 13:34:17

I have a table with the following column names:
RecID, Slot1, Slot2, Slot3, Slot4, Slot5.

I am taking data from another table and based on it's value I need to update the appropriate column above.

If the value is 'blue' then I need to update Slot1
If the value is 'orange' then I need to update Slot2, etc...

Is something like this possible with a straight update statement, or do I need to use dynamic sql?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 13:41:45

something like this:-

UPDATE t
SET t.Slot1=CASE WHEN colvalue='blue' THEN your new value END,
t.Slot2=CASE WHEN colvalue='orange' THEN yournewvalue END,
...
FROM YourTable t
JOIN ....

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2008-04-16 : 13:45:06

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 15:46:29

Don't forget the ELSE parts!!! Otherwise all records not matching will be set to NULL!!!

UPDATE t
SET t.Slot1=CASE WHEN colvalue='blue' THEN your new value ELSE t.Slot1 END,
t.Slot2=CASE WHEN colvalue='orange' THEN yournewvalue ELSE t.Slot1 END,
...
FROM YourTable t
JOIN ....

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources