Forum

Forums
PL SQL
Interview Questions...
How to Delete Dupli...
 
Share:
Notifications
Clear all

How to Delete Duplicate Records in SQL Without Using ROWID?

 
Interview Questions & Answers
Last Post by SQL Admin 8 months ago
1 Posts
1 Users
0 Reactions
1,424 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jul 24, 2025 2:18 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

How to Delete Duplicate Records in SQL Without Using ROWID?

How can I delete duplicate records from a SQL table without using ROWID or Common Table Expressions?

Yes, you can delete duplicate records from a table even without using `ROWID` or `CTE`. One effective way is by using the `GROUP BY` and `HAVING` clauses along with a temporary table. Here's how:

🔁 Step-by-Step Example:

Suppose you have a table called `employees`:

CREATE TABLE employees (
id INT,
name VARCHAR(50),
department VARCHAR(50)
);

And it has duplicate rows like this:

| id | name | department |
|----|----------|------------|
| 1 | John | HR |
| 2 | Alice | IT |
| 1 | John | HR |
| 3 | Robert | IT |

 ✅ Method to Delete Duplicates Without ROWID:

CREATE TABLE temp_employees AS
SELECT MIN(ROWNUM) as rn, id, name, department
FROM employees
GROUP BY id, name, department;

DELETE FROM employees;

INSERT INTO employees
SELECT id, name, department FROM temp_employees;

DROP TABLE temp_employees;

✅ Alternate Simple Approach (for MySQL):

DELETE e1 FROM employees e1
JOIN employees e2
ON e1.id = e2.id AND e1.name = e2.name AND e1.department = e2.department
WHERE e1.ROWID > e2.ROWID;

⚠️ Note: The above uses `ROWID`, so skip this if you need a non-ROWID version as per requirement.


This topic was modified 6 months ago by SQL Admin
Topic Tags
delete duplicate records remove duplicates sql wi sql delete duplicates or delete duplicates withou remove duplicate rows my

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  delete duplicate records (1) , remove duplicates sql wi (1) , sql delete duplicates or (1) , delete duplicates withou (1) , remove duplicate rows my (1) ,
Share: