To remove duplicated rows from Oracle, I tend to use the following:

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

(taken from SO)

However, I'd like to remove the same rows in a slightly different scenario. Image there is a table with rows: name, surname. Duplicate row is when

  1. name is the same, and
  2. surname is the same, or starts with + (plus sign) and then is the same.

For example, the following is to be considered the same:

name: John, surname Doe

name: John, surname +Doe

How do I remove those duplicated rows?

1 Answer 1

WITH cte AS(
SELECT rowid, name, 
CASE WHEN surname NOT LIKE '+%'
THEN '+' + surname
ELSE surname
END AS surname
FROM your_table)

DELETE FROM your_table
(SELECT MIN(rowid)
 FROM cte
 GROUP BY name, surname

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.