Laurent Hinoul

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.” - John F. Woods

How to delete duplicate rows in SQL

To find and delete duplicate rows from a table, you can execute the query below. Note that this is for Oracle. You can use it for other databases but you need to change the ‘rowid’ then.

You need to change the ‘table_name’ by your table name an the ‘column_name’ by your column you want to check on.

To explain it simply, I will show an example.

Take, for example, a table named persons. In that table, there are the following fields: id, username, first_name, last_name, emailaddress

Lets say we want to check that username and emailaddress are unique, we need to use the following query:

Don’t replace rowid by id. In oracle rowid will always be 1,2,3,4… If you want to use it for another sql language, search google for rowid alternatives.

Result:

  • Person[id=123, username=laurent, first_name=laurent, last_name=hinoul, emailaddress=info@laurenthinoul.com]
  • Person[id=456, username=laurent, first_name=test, last_name=test, emailaddress=info@laurenthinoul.com]

The first one wil be kept and the last one will be removed because username and emailaddress are equal and the first rowid is less than the second one.

Leave a Reply

%d bloggers like this: