Update column by comparing column 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 column by comparing column values

Author

Topic

goodsolution
Starting Member

38 Posts

Posted - 2009-05-08 : 20:33:34

I have a table and it is having records as follows

col1 col2 col3 col4
ABC ABC ABC ABC
DEF GHI GHI GHI
JKL MNO PQR PQR
STU KAN ANN BCN

I want o/p as
col1 col2 col3 col4
ABC NULL NULL NULL
DEF GHI NULL NULL
JKL MNO PQR NULL
STU KAN ANN BCN

So what exactly i want is, i have to compare each column value and if column value is repeating it should be updated as NULL value.

-Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-08 : 21:12:23

[code]UPDATE myTable SET col2=CASE WHEN col2=col1 THEN Null ELSE col2 END,
col3=CASE WHEN col3=col2 THEN Null ELSE col3 END,
col4=CASE WHEN col4=col3 THEN Null ELSE col4 END[/code]This does not handle cases where the same values are in non-adjacent columns, since you didn't specify if you had such data or wanted it changed. If so, it's easy to extend the CASE expressions to compare additional columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:46:57

[code]UPDATE Table
SET Col2=NULLIF(Col2,Col1),
Col3=NULLIF(Col3,Col2),
Col4=NULLIF(Col4,Col3)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources