In SQL, some rows contain duplicate entries in a column. For deleting such rows, we need to use the DELETE keyword along with self-joining the table with itself. The same is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table BONUSES inside the database GeeksForGeeks. This table has 3 columns namely EMPLOYEE_ID, EMPLOYEE_NAME, and EMPLOYEE_BONUS containing the id of the employee, the name of the employee, and his/her bonus.

Query:

CREATE TABLE BONUSES(
EMPLOYEE_ID INT,
EMPLOYEE_NAME VARCHAR(10),
EMPLOYEE_BONUS INT);

Output:

Step 4: Describe the structure of the table BONUSES.

Query:

EXEC SP_COLUMNS BONUSES;

Output:

Step 5: Insert 10 rows into the BONUSES table.

Query:

INSERT INTO BONUSES VALUES(1,'RAJ',10000);
INSERT INTO BONUSES VALUES(2,'RAJIV',10000);
INSERT INTO BONUSES VALUES(3,'RAJ',20000);
INSERT INTO BONUSES VALUES(4,'SAMEER',20000);
INSERT INTO BONUSES VALUES(5,'PANKAJ',30000);
INSERT INTO BONUSES VALUES(6,'HARRY',30000);
INSERT INTO BONUSES VALUES(7,'VAUN',40000);
INSERT INTO BONUSES VALUES(8,'SANGWOO',40000);
INSERT INTO BONUSES VALUES(9,'SAM',50000);
INSERT INTO BONUSES VALUES(10,'TIM',50000);

Output:

Step 6: Display all the rows of the BONUSES table.

Query:

SELECT * FROM BONUSES;

Output:

Step 7: Delete rows from the table BONUSES which have duplicate entries in the column EMPLOYEE_BONUS. To achieve this, we use the DELETE function by self joining(use JOIN function on 2 aliases of the table i.e. B1 and B2) the table with itself and comparing the entries of the column EMPLOYEE_BONUS for different entries of the column EMPLOYEE_ID because ID is unique for each employee.

Syntax:

DELETE T1 FROM TABLE_NAME T1
JOIN TABLE_NAME T2
ON T1.COLUMN_NAME1 = T2.COLUMN_NAME1
AND T2.COLUMN_NAME2 < T1.COLUMN_NAME2;

Query:

DELETE B1 FROM BONUSES B1
JOIN BONUSES B2
ON B1.EMPLOYEE_BONUS = B2.EMPLOYEE_BONUS
AND B2.EMPLOYEE_ID < B1.EMPLOYEE_ID;

Output:

Step 8: Display all the rows of the updated BONUSES table.

Query:

SELECT * FROM BONUSES;

Note – No row has duplicate entries in the column EMPLOYEE_BONUS.

Output:

Step 9: Delete rows from the table BONUSES which have duplicate entries in the column EMPLOYEE_NAME.

Query:

DELETE B1 FROM BONUSES B1
JOIN BONUSES B2
ON B1.EMPLOYEE_NAME = B2.EMPLOYEE_NAME
AND B2.EMPLOYEE_ID < B1.EMPLOYEE_ID;

Output:

Step 10: Display all the rows of the updated BONUSES table.

Query:

SELECT * FROM BONUSES;

Note: No row has duplicate entries in the column EMPLOYEE_NAME.

Output: