De-duping a table using a CTE

DECLARE @duplications TABLE (value1 VARCHAR(3),value2 VARCHAR(3))

INSERT INTO @duplications VALUES
(‘AAA’,’BBB’),
(‘AAA’,’BBB’),
(‘BBB’,’BBB’),
(‘AAA’,’CCC’),
(‘BBB’,’BBB’)

SELECT * FROM @duplications;

WITH cte AS(
SELECT *,ROW_NUMBER() OVER (PARTITION BY value1,value2 ORDER BY (SELECT NULL)) AS rn
FROM @duplications)
DELETE FROM cte
WHERE rn!=1

SELECT * FROM @duplications

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: