SQL Remove Duplicate Rows: A How-To Guide

[@PanamaPUA]

Take a lot time finding the right video

[@DatabaseStar]

Glad you found this one!

[@naanlopkromtit]

If you accidentally delete some of the duplicated rows from your table, what is a statement that I can run to add the columns back to my table?

[@DatabaseStar]

If you want to add the deleted rows back to your table, you could try a Rollback statement if you still have your window open. There may be other database features (such as Oracle's FLASHBACK) that allow you to get the old data. Otherwise you could restore from backup if you have any.

[@naanlopkromtit]

@@DatabaseStar I just tried the rollback function and that seemed to make it worse. The problem is I forgot to save the query I used when I removed the duplicate columns. I tried going thru my sql query history but that didn’t work. So now I’m stuck. I’m considering making a new table altogether

[@vicvic553]

The first method doesn't work in my case... I have got a different example, i.e. a different database, but this code: delete from contacts c inner join contacts d on c.first_name = d.first_name and c.last_name = d.last_name and c.email = d.email where c.contact_id < d.contact_id; gives me an error: 'Incorrect syntax near 'c'.' Actually, it is a correct syntax, imo. If I changed the 'delete from' word into 'select', everything works, but I want to delete and this doesn't work and I have no clue why. Could you help me, please?

[@DatabaseStar]

Sure, which database are you using (Oracle, SQL Server, MySQL, PostgreSQL)?

[@vicvic553]

@@DatabaseStar SQL Server and I still haven't solved this issue.

[@rayzamuhamar5538]

@@vicvic553 try like this, delete c from contacts c

[@MrJonhSmithTheFirst]

Same thing with ORACLE , all fine with SELECT statement though. But delete gives "ORA-00933: SQL command not properly ended "

[@MrJonhSmithTheFirst]

@@rayzamuhamar5538 didn't work for me

[@DraikM]

kept getting a syntax error for the INNER JOIN part, not sure what was wrong but this didn't work

[@DatabaseStar]

What syntax error are you getting, and what database are you using?

[@berthagarcia3371]

Does this work if we need to delete duplicates from multiple tables?

[@DatabaseStar]

How would you identify if something is a duplicate if it's in multiple tables? I don't know if these methods would work but you can try it.

[@Anilkumar-reddy]

Hi, I am having scenerio, I am having table having duplicate records, Based on date, i want to make one record as inactive record. How to do that?. Please help

[@DatabaseStar]

Hi, you should be able to use the methods mentioned in this video to identify the duplicate.

[@rohitsethi5696]

hello where id came this is not exist in table

[@DatabaseStar]

The ID is in the table in this example. You'll need a way to identify a row in a table which is what the ID does.

[@abhay6276]

WHAT IF FIRST_NAME , LAST_NAME AND ID ARE SAME FOR EMPLOYEES? WHAT THE QUERY TO BE USED?

[@DatabaseStar]

You could use a ROWID field (if you're on Oracle) or another function such as ROW_NUMBER. I've written a few methods that may work in this article: www.databasestar.com/sql-remove-duplicates/

[@arunashok228]

@@DatabaseStar But in ROW_NUMBER method you are using ID.

[@arunashok228]

Looking for a solution where even id is same (MySQL)

[@LA-sw1gv]

Inner Join is definitely the least complicated version

[@DatabaseStar]

Yeah I agree

[@sindhuvi5236]

can someone explain why we using here p1.id < p2.id? I find it confusing

[@DatabaseStar]

Good question. This is done to eliminate duplicates. We use the id field to find records that have an id value less than another id value so that only one record that matches the other criteria is found. Does that help?

[@bfonsecat]

@@DatabaseStar So it only works for sequential ids, right?

[@ereshadul]

How about using window function to delete the duplicate data?

[@DatabaseStar]

Good idea, you could use one to identify duplicate rows to delete.

[@ereshadul]

@@DatabaseStar actually i tried and not succeed. ~~~~~~delete from emp ~~~~~~where (select row_number() over(partition by sal order by sal) from emp) >1 emp table has ID , ename and sal colum. error msg: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

[@brianferris1]

Great stuff. I have to figure how to do this after I append 3 queries using a CTE.

[@DatabaseStar]

Yeah once you start combining things like that it can get complicated.

[@ahmedalrawi9687]

thank you

[@DatabaseStar]

You're welcome!

[@vickypatekar9532]

Thank You Tooooooo Help ful

[@DatabaseStar]

You’re welcome!