sql group by update help - 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 2005 Forums

Transact-SQL (2005)

sql group by update help

Author

Topic

dinesh_krish
Starting Member

2 Posts

Posted - 2008-11-01 : 21:25:43

Thanks in advance for your help. I need help with SQL update query. Need to update changestatus column based on the condiions below.

Custid custloc newcustid newcustloc changestatus
100 200 90 70 ?
101 201 90 70 ?
102 203 91 72 ?
103 204 91 73 ?

update 1st 2 rows with changestatus as 1 based on newcustid and newcutloc is same and custid and custloc are different values

update 2nd and 3rd rows with changestatus as 2 based on newcustid is the same value but not newcustloc,custid,custloc.

Your help is apperciated. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:49:07

[code]UPDATE t
SET t.changestatus=CASE WHEN t1.newcustid IS NOT NULL THEN 1
WHEN t1.newcustid IS NULL
AND t2.newcustid IS NOT NULL THEN 2
ELSE t.changestatus
END
FROM Table t
LEFT JOIN (SELECT newcustid,newcustloc
FROM Table
GROUP BY newcustid,newcustloc
HAVING COUNT(*) >1)t1
ON t1.newcustid=t.newcustid
AND t1.newcustloc=t.newcustloc
LEFT JOIN (SELECT newcustid
FROM Table
GROUP BY newcustid
HAVING COUNT(*) >1)t2
ON t2.newcustid=t.newcustid[/code]

dinesh_krish
Starting Member

2 Posts

Posted - 2008-11-02 : 19:26:53

Awesome !.. Thank you very much. Works perfect.

quote:

Originally posted by visakh16

UPDATE t
SET t.changestatus=CASE WHEN t1.newcustid IS NOT NULL THEN 1 
                        WHEN t1.newcustid IS NULL 
                         AND t2.newcustid IS NOT NULL THEN 2
                        ELSE t.changestatus
                   END
FROM Table t
LEFT JOIN (SELECT newcustid,newcustloc 
            FROM Table
            GROUP BY newcustid,newcustloc 
            HAVING COUNT(*) >1)t1
ON t1.newcustid=t.newcustid
AND t1.newcustloc=t.newcustloc
LEFT JOIN (SELECT newcustid 
            FROM Table
            GROUP BY newcustid
            HAVING COUNT(*) >1)t2
ON t2.newcustid=t.newcustid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 23:37:23

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources