SQL Puzzle | Delete Duplicate Records from a Table but one

In this puzzle you have two tables – a1 and a2. I want to remove duplicates from table a2 which have a entry in a1 table. Please check out the sample input values and sample expected output below.

Sample Input

Table – A1

Table – A2

Expected Output – Table A2

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE A1
(
	ID INT
)
GO

INSERT INTO A1 VALUES (1),(2)
GO

CREATE TABLE A2
(
	ID INT
)
GO

INSERT INTO A2 VALUES (1),(2),(1),(3),(3)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1

--

;WITH CTE AS 
(
	SELECT *, ROW_NUMBER () OVER (PARTITION BY Id ORDER BY Id) row_num from A2
)
DELETE x FROM a2 x 
INNER JOIN a1 on a1.Id = x.Id 
INNER JOIN CTE on x.Id = cte.Id 
WHERE CTE.row_num <> 1

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com