Use subquery to delete duplicate rows
SELECT *
FROM employees a
WHERE ROWID > (SELECT MIN(ROWID)
FROM employees b
WHERE b.first_name = a.first_name
AND b.last_name = a.last_name);
SELECT *
FROM employees a
WHERE a.rowid > ANY (SELECT b.rowid
FROM employees b
WHERE a.first_name = b.first_name
AND a.last_name = b.last_name);
DELETE FROM employees a
WHERE a.rowid > ANY (SELECT b.rowid
FROM employees b
WHERE a.first_name = b.first_name
AND a.last_name = b.last_name);
Use RANK
to delete duplicate rows
SELECT *
FROM employees
WHERE ROWID IN (SELECT "rowid"
FROM (SELECT "rowid", rank_n
FROM (SELECT first_name,
last_name,
rank() over(PARTITION BY first_name, last_name ORDER BY ROWID) rank_n,
ROWID AS "rowid"
FROM employees
WHERE (first_name, last_name) IN
(SELECT first_name, last_name
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1)))
WHERE rank_n > 1);
DELETE FROM employees
WHERE ROWID IN (SELECT "rowid"
FROM (SELECT "rowid", rank_n
FROM (SELECT first_name,
last_name,
rank() over(PARTITION BY first_name, last_name ORDER BY ROWID) rank_n,
ROWID AS "rowid"
FROM employees
WHERE (first_name, last_name) IN
(SELECT first_name, last_name
FROM employees
GROUP BY first_name,
last_name
HAVING COUNT(*) > 1)))
WHERE rank_n > 1);
Use
analytics to delete duplicate rows
SELECT *
FROM employees
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID,
row_number() over(PARTITION BY first_name, last_name ORDER BY first_name,
last_name) dup
FROM employees)
WHERE dup > 1);
DELETE FROM employees
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID,
row_number() over(PARTITION BY first_name, last_name ORDER BY first_name,
last_name) dup
FROM employees)
WHERE dup > 1);
Removing
duplicate table rows where rows have NULL values
SELECT *
FROM employees a
WHERE a.rowid > ANY
(SELECT b.rowid
FROM employees b
WHERE (a.first_name = b.first_name OR
(a.first_name IS NULL AND b.first_name IS NULL))
AND (a.manager_id = b.manager_id OR
(a.manager_id IS NULL AND b.manager_id IS NULL)));
DELETE FROM employees a
WHERE a.rowid > ANY
(SELECT b.rowid
FROM employees b
WHERE (a.first_name = b.first_name OR
(a.first_name IS NULL AND b.first_name IS NULL))
AND (a.manager_id = b.manager_id OR
(a.manager_id IS NULL AND b.manager_id IS NULL)));
I hope you all have enjoyed reading this article. Comments are welcome....
