Search code examples
sqlsql-serverduplicatesmultiple-tablesdeduplication

Removing tables with a join in SQL Server

I'm new to this DBA thing and I've been tasked with removing duplicates from a couple of tables. I'm working in SQL Server. They all have a field called LAST_UPD that tracks their last update. All the tables join to TABLE1 and each user is identified by TABLE1.ALIAS_KEY. The join is below. I'm wondering how to clean out all the duplicate rows and keep the most recent entries from Table2. Thanks for the help!

FROM dbo.Table1 T1
LEFT JOIN dbo.Table2 T2
    ON (T1.PARROW = T2.PARROW)
Solution

  • Assuming (and I might be incorrect) that last updated is unique you can do:

    DELETE FROM dbo.Table2 T2 WHERE T2.LAST_UPD NOT IN
    (SELECT max(LAST_UPD) FROM dbo.Table2 T2 
        LEFT JOIN dbo.Table1 T1 ON (T1.PARROW = T2.PARROW)
        GROUP BY T1.ALIAS_KEY)