How to Remove Duplicate Values Based on Only One Column of the Table in SQL?

To remove duplicate values based on only one column of the table in SQL, use the DELETE command along with self-join on the table.

Self-Join helps in finding the duplicate values in the table, which can be deleted using the DELETE query.

Follow this step-by-step instructions to delete duplicate values of the table in SQL.

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

Query:

CREATE DATABASE w3wiki;

Output:

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

Query:

USE w3wiki;

Output:

Step 3: Create a table BONUSES inside the database w3wiki. 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: Insert 10 rows into the BONUSES table.

Query:

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

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

Query:

SELECT * FROM BONUSES;

Output:

Step 9: Delete rows from the table BONUSES which have duplicate entries in the column EMPLOYEE_NAME. 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_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;

Output:

Duplicate values has been removed from the column EMPLOYEE_NAME.