How to Eliminate Duplicate Values Based on Only One Column of the Table in SQL?
Last Updated :
28 Nov, 2021
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:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...