Quick Way to Delete Duplicate Records from a Table

Posted: March 28, 2011 in SQL Server 2005

This routine will remove duplicate records from a table so that only the latest record remains for the condition:

-- Declare table variable
DECLARE @tbl_email TABLE
       (id             INT IDENTITY(1,1),
        customer_id    VARCHAR(100),
        customer_email VARCHAR(100));

-- Insert records into table variable
INSERT INTO @tbl_email (customer_id, customer_email) VALUES ('A', 'a@emai.com');
INSERT INTO @tbl_email (customer_id, customer_email) VALUES ('B', 'b@emai.com');
INSERT INTO @tbl_email (customer_id, customer_email) VALUES ('A', 'a@emai.com');
INSERT INTO @tbl_email (customer_id, customer_email) VALUES ('B', 'b@emai.com');
INSERT INTO @tbl_email (customer_id, customer_email) VALUES ('A', 'a@emai.com');

-- Select the details
SELECT te.*
FROM   @tbl_email te

-- Delete the duplicates
DELETE
FROM   @tbl_email
WHERE  id NOT IN (SELECT   MAX(te.id)
                  FROM     @tbl_email te
                  GROUP BY te.customer_id);

-- Select the details
SELECT te.*
FROM   @tbl_email te;
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