Compare rows from two tables. - 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)

Compare rows from two tables.

Author

Topic

nasty84
Starting Member

6 Posts

Posted - 2010-04-05 : 12:15:35

I need to compare following two tables. Need to compare amounts for groupID from two tables. If amounts are different Keep the records in table 1, otherwise delete from table1. In table 2, a groupID can have more than one record, but need to consider only latest one in comparing. For eg: In the following table 2,GroupID 101 has 2 records, but need to consider only record with ID 21 because is the latest one in the table.

ID GroupID Amount
1 101 100
2 102 200
3 103 300

ID GroupID Amount
20 101 100
21 101 150
22 102 400
23 103 300

Thanks for your help and time..

Sam.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:19:28

[code]
DELETE t
FROM Table1 t
INNER JOIN Table2 t1
ON t1.GroupID=t.GroupID
INNER JOIN (SELECT GroupID,MAX(ID) AS Latest
FROM Table2
GROUP BY GroupID)t2
ON t2.GroupID=t1.GroupID
AND t2.Latest = t1.ID
WHERE t.Amount=t1.Amount
[/code]

suggest you to first do a SELECT and see if you get intended records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nasty84
Starting Member

6 Posts

Posted - 2010-04-05 : 12:29:51

It worked...
Thank you very much for your help and time..
I appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:31:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources