161 ) SSMS : Delete Duplicate Records

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2) Delete Duplicate Records for SSMS : 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question: Write a query to delete duplicate records from a table while keeping one unique instance.

Query: 

  FOR SSMS : 

 

DROP TABLE employees; -- if exists
CREATE TABLE employees
  (
     employeeid INT,
     firstname  VARCHAR(50) NOT NULL,
     lastname   VARCHAR(50) NOT NULL,
     email      VARCHAR(100) NOT NULL
  );

------------------------------------------------------------------------------
SELECT *
FROM   employees;

------------------------------------------------------------------------------
--truncate table employees;
------------------------------------------------------------------------------
 

 

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email) VALUES

(1, 'John', 'Doe', 'john.doe@example.com'),       -- Original 1

(1, 'John', 'Doe', 'john.doe@example.com'),       -- Duplicate (Same ID, Name, Email)

(2, 'Jane', 'Smith', 'jane.smith@example.com'),     -- Original 2

(3, 'Robert', 'Johnson', 'rob.j@example.com'),    -- Original 3

(3, 'Robert', 'Johnson', 'rob.j@example.com'),    -- Duplicate (Same ID, Name, Email)

(4, 'Emily', 'Davis', 'emily.d@example.com'),     -- Original 4

(5, 'Michael', 'Brown', 'mike.b@example.com'),    -- Original 5

(2, 'Jane', 'Smith', 'jane.smith@example.com'),     -- Duplicate (Same ID, Name, Email)

(6, 'Sarah', 'Wilson', 'sarah.w@example.com'),    -- Original 6

(6, 'Sarah', 'Wilson', 'sarah.w@example.com');    -- Duplicate (Same ID, Name, Email)

------------------------------------------------------------------------------
-- step 1 : 
-- -----------------------------------------
-- BEGIN TRANSACTION;
WITH temp_dup
     AS (SELECT employeeid,
                firstname,
                lastname,
                email,
                Row_number()
                  OVER (
                    partition BY employeeid
                    ORDER BY employeeid ) AS RowNum
         FROM   employees)
DELETE FROM temp_dup
WHERE  rownum > 1;

--- ---------------------------------------------------------------------------
-- select * from Employees;
-- ROLLBACK TRANSACTION; 
SELECT *
FROM   employees; 

----------------------------------------------------

----  ouput with dups --- 

----------------------------------------------------

EmployeeID FirstName LastName Email

1 John Doe john.doe@example.com

1 John Doe john.doe@example.com

1 John Doe john.doe@example.com

2 Jane Smith jane.smith@example.com

3 Robert Johnson rob.j@example.com

4 Emily Davis emily.d@example.com

3 Robert Johnson rob.j@example.com

2 Jane Smith jane.smith@example.com

6 Sarah Wilson sarah.w@example.com

3 Robert Johnson rob.j@example.com

4 Emily Davis emily.d@example.com

5 Michael Brown mike.b@example.com

5 Michael Brown mike.b@example.com

2 Jane Smith jane.smith@example.com

6 Sarah Wilson sarah.w@example.com

6 Sarah Wilson sarah.w@example.com

----------------------------------------------------

----  ouput without dups --- 

----------------------------------------------------

EmployeeID FirstName LastName Email

1 John Doe john.doe@example.com

2 Jane Smith jane.smith@example.com

3 Robert Johnson rob.j@example.com

4 Emily Davis emily.d@example.com

5 Michael Brown mike.b@example.com

6 Sarah Wilson sarah.w@example.com

-- ----------------or BELOW ALSO WORKS ------------------------- 

-- BEGIN TRANSACTION;
WITH temp_dup
     AS (SELECT e.*,--  EmployeeID, FirstName, LastName, Email,
                Row_number()
                  OVER (
                    partition BY employeeid
                    ORDER BY employeeid ) AS RowNum
         FROM   employees e)
DELETE FROM temp_dup
WHERE  rownum > 1;

--- ---------------------------------------------------------------------------
SELECT *
FROM   employees;
-- ROLLBACK TRANSACTION;
-- select * from Employees;

--- ---------------------------------------------------------------------------