Chasen Bettinger Chasen Bettinger - 5 months ago 9
SQL Question

Deleting Specific Duplicate Records

I am trying to delete duplicate records that do not contain a specific status.

Here is my code:

DELETE FROM [dbo].[test]
WHERE ID NOT IN ( SELECT MAX(ID)
FROM [dbo].[test]
GROUP BY [UserID] )
AND Status <> 'D'


I need to delete all duplicate records except the records that contain the status 'D'. The error that it is giving me is incorrect syntax.

The ultimate problem is that I am inserting data from two tables. One table, Support, inserts information that needs to update the master table, Data. The other table, Identity, provides the user database for the master table. If the status from the support table reads 'D' for Disable, I need to keep both records in the master table.

Answer

Do you mean this ?

DELETE FROM [dbo].[test] 
WHERE  Status <> 'D' AND  ID NOT IN ( 
SELECT MAX(ID) 
FROM [dbo].[test] 
GROUP BY [UserID]) 
Comments