Internet solutions team

Delete duplicate records SQL

How to delete duplicate Towns from Town Table  Using SQL Script where Primary key is unique but Town are duplicate.

SQL Server and MY SQL

DELETE Town1
FROM  Town Town1
INNER JOIN Town Town2 ON Town2 .TownName=Town1.TownName
WHERE
Town1.TownID >Town2.TownID

Above SQL Script will delete Duplicate Towns from a Twon table where Town ID  is unique but Town name are duplicated

Scenario 1, Delete all Towns which  are not being used by Address Table if they are duplicate in Town Table. That is we need to keep one record for town.

Step 1 get all Town IDs in a temp tbale #noaddress which are not present in Address table

SELECT  TownID
INTO #noaddress
FROM [Town]
EXCEPT
SELECT TownID FROM  [Address]

Setp 2, Delete all duplicate towns from Town table which are not present in Address table

DELETE Town1
FROM  Town Town1

INNER JOIN Town Town2 ON Town2 .TownName=Town1.TownName

NNER JOIN #noaddress on Town1.TownID=#noaddress.TownID

WHERE
Town1.TownID >Town2.TownID

Be Sociable, Share!

• 

Tags: Delete duplicate records SQL, My SQL, SQL Serer