SQL Server 2008 R2: Delete duplicate rows from multiple tables and keep original one

Question

I have the 5 employee tables as shown below.

---Table: Emp_1
CREATE TABLE Emp_1 
(
    Emp_ID VARCHAR(10),
    Emp_FName VARCHAR(10),
    Emp_LName VARCHAR(10),
    Emp_PNumber VARCHAR(10)
);

---Table: Emp_2
CREATE TABLE Emp_2
(
    Emp_ID VARCHAR(10),
    Emp_FName VARCHAR(10),
    Emp_LName VARCHAR(10),
    Emp_PNumber VARCHAR(10)
);

---Table: Emp_3
CREATE TABLE Emp_3 
(
    Emp_ID VARCHAR(10),
    Emp_FName VARCHAR(10),
    Emp_LName VARCHAR(10),
    Emp_PNumber VARCHAR(10)
);

---Table: Emp_4
CREATE TABLE Emp_4 
(
    Emp_ID VARCHAR(10),
    Emp_FName VARCHAR(10),
    Emp_LName VARCHAR(10),
    Emp_PNumber VARCHAR(10)
);

---Table: Emp_5
CREATE TABLE Emp_5
(
    Emp_ID VARCHAR(10),
    Emp_FName VARCHAR(10),
    Emp_LName VARCHAR(10),
    Emp_PNumber VARCHAR(10)
);

--Insertion: Emp_1
INSERT INTO Emp_1 VALUES('A1','Abram','Mak','123');
INSERT INTO Emp_1 VALUES('A2','Sam','William','321');

--Insertion: Emp_2
INSERT INTO Emp_2 VALUES('A3','John','Marsh','456');
INSERT INTO Emp_2 VALUES('A4','Tom','Lee','654');

--Insertion: Emp_3
INSERT INTO Emp_3 VALUES('A5','Abram','Mak','789');
INSERT INTO Emp_3 VALUES('A6','Shawn','Meben','987');

--Insertion: Emp_4
INSERT INTO Emp_4 VALUES('A7','Sam','William','189');
INSERT INTO Emp_4 VALUES('A8','Mark','Boucher','287');

--Insertion: Emp_5
INSERT INTO Emp_5 VALUES('A9','Gery','Joy','907');
INSERT INTO Emp_5 VALUES('A10','Anthony','Desosa','977');

Now I will insert the each table name into Container table. I have the following table called as Container which contains the table names, which may be many in my case I have just inserted 5 as shown below.

--Table : Container
CREATE TABLE Container
(
    TableName VARCHAR(50)
);

--Insertion
INSERT INTO Container VALUES('Emp_1');
INSERT INTO Container VALUES('Emp_2');
INSERT INTO Container VALUES('Emp_3'); 
INSERT INTO Container VALUES('Emp_4');
INSERT INTO Container VALUES('Emp_5');

Note: Now I want to delete the duplicate row from each table and want to keep the original as it is. And the condition for delete the duplicate row is:

If the Emp_FName and Emp_LName is match with the other tables then the duplicated row has to be deleted and original row keep as it is.

In my example the Emp_FName and Emp_LName : 'Abram','Mak' repeated in the table Emp_3 which has to be deleted and original one which is present in the table Emp_1 has keep as it is.

And Emp_FName and Emp_LName : 'Sam','William' repeated in the table Emp_4 which has to be deleted and original one which is present in the table Emp_1 has keep as it is.

For single Table: For single table I can use the following script to delete the duplicate one and keep the original one.

;WITH CTE AS
(
      SELECT  *,ROW_NUMBER() OVER (PARTITION BY Emp_FName,Emp_LName ORDER BY Emp_FName) Row_Number FROM Emp_1
)
DELETE FROM CTE
WHERE  Row_Number > 1;

My question is how to delete duplicate row from the multiple tables which are dynamic I mean in my Container table the tables may be more than 5 also.

Did you do any searching? For example: http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields or https://support.microsoft.com/en-us/kb/139444 — Leptonator, Aug 19 '15 at 05:33
@Leptonator, Yeah! I did. But didn't get the expected one. In my case I have multiple tables from which I need to find duplicate delete those. Your link works for single table. — MAK, Aug 19 '15 at 05:35
So what is the problem? Work with table 1 and 2, and so on.. It does not seem that big of deal.. — Leptonator, Aug 19 '15 at 05:37
So if you have records in 2 tables how do you know from which table to delete? — Pradeep Kumar, Aug 19 '15 at 05:59
Are you interested in getting one consolidated table that doesn't have duplicate records? — Pradeep Kumar, Aug 19 '15 at 06:06
delete from emp3 inner join imp1 on imp1.fname = imp3.fname and emp1.name = emp3.lname. if they are dynamic and cannot change that.... use exec sp_executesql 'your query' and build a delete query. — Julien Vavasseur, Aug 19 '15 at 06:13
@MAK how do you know which one record is `original`? If records match in 2, 3 and 4 tables, so how do you know what should be deleted? Also can have in the same table duplicates rows? Then what should be done? — Stanislovas Kalašnikovas, Aug 19 '15 at 06:16
@StanislovasKalašnikovas, May be using `ROW_NUMBER()` function. In my case If record match in table 1 and table 2 then the I need to find the row number for those records if it is >1 then delete it. — MAK, Aug 19 '15 at 06:21
@JulienVavasseur, Yup! I need to prepare dynamic delete statement for that, which is I not getting. — MAK, Aug 19 '15 at 06:23
@PradeepKumar, Yes! I just want to get job done which doesn't hit the performance on large data. — MAK, Aug 19 '15 at 06:24
So in real your table names are like: `Emp_1`, `Emp_2` and so on? If yes, than priority of original data is depended by number? If duplicates represents in `Emp_2` and `Emp_3` It should be deleted from `Emp_3` and saved in `Emp_2`? — Stanislovas Kalašnikovas, Aug 19 '15 at 06:30
@StanislovasKalašnikovas, Yes! In my post I have mentioned that. In table `Emp_1` and `Emp_3` the record is repeated I mean duplicated. In such condition I want to delete it from only table `Emp_3`. — MAK, Aug 19 '15 at 06:37

score 1 · Accepted Answer · answered Aug 19 '15 at 07:17

This query will delete it (emp1 > emp2 ... > emp5):

Declare @sql nvarchar(max) = ''

Select @sql = coalesce(@sql, '')+ '
Delete d From ['+c2.TableName+'] as d
    Inner join ['+c1.TableName+'] as c on c.Emp_FName = d.Emp_FName and c.Emp_LName = d.Emp_LName; 
    '
From Container as c1
Inner Join Container as c2 On c2.TableName > c1.TableName
Order By c1.TableName, c2.TableName

Print @sql
EXEC sp_executesql @sql

However, I think you should take some time to think about your system and data model and try to find a better way of doing it without using dynamic queries.

Yes! It does the job and yeah I will think about it try to do it without using dynamic query. Thank you so much for that. — MAK, Aug 19 '15 at 07:26

SQL Server 2008 R2: Delete duplicate rows from multiple tables and keep original one

1 Answers1