How to delete duplicate records from a table in SQL | How to delete duplicate rows in SQL

How to delete duplicate records from a table in SQL | Multiple ways to delete duplicate records in SQL
In this video, multiple ways has been shown to delete duplicate records from a table.
1. By using GROUP BY clause and MAX()
2. By using CTE and ROW_NUMBER()
Follow me on Social Networking Sites :
*Twitter:
/ sunilkr5672
*Instagram :
/ itjunction4all
Input Script :
Create Table EmpDetail (
ID int identity(1,1),
EmpName varchar(25),
Departmemt varchar(20),
Age int,
Gender char(1),
Salary Bigint
)
Insert into EmpDetail values('James','HR',30,'M',40000)
Insert into EmpDetail values('James','HR',30,'M',40000)
Insert into EmpDetail values('James','HR',30,'M',40000)
Insert into EmpDetail values('John','Finance',32,'M',45000)
Insert into EmpDetail values('Maria','Admin',28,'M',30000)
Insert into EmpDetail values('Maria','Admin',28,'M',30000)
Insert into EmpDetail values('Mark','Account',35,'M',50000)
#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ETLTesting
#DeleteDuplicateRecordsFromTable #ITJunction4All

[@yonashabtesllassie5642]

Very good explanation, very clear and cleaver way.... Thank you it helped me a lot

[@ItJunction4all]

You are welcome! Thank you for such a nice feedback !

[@tonygiovanni5761]

Good stuff. I like the way you make easy. Brief and straight to the point. Keep posting more videos

[@ItJunction4all]

Thanks a lot Tony ! Please subscribe my channel so that you will get all my future videos updates

[@SamuelLed2k24]

thank you! been working on the same problem for a while now, and I have finally solved it because of this video.

[@ItJunction4all]

You are welcome! I am glad that my video helped you.

[@rajeshSharma-od4sn]

Nicely explained by you sir
Very helpful video
Thank you sir

[@ItJunction4all]

Thanks Rajesh ! I hope you will like my other videos as well.

[@dhruvchaudhary_7]

What if we use distinct function? Our output would be the same or not?

[@akpaneno9643]

This was really helpful.

[@ItJunction4all]

You are welcome 😍😍

[@sumancreative2806]

Nice explanation.. Thank You...

[@ItJunction4all]

You are welcome Suman !

[@masheTyMasha]

Thank you so much! Very loligicall explained.

[@ItJunction4all]

You're very welcome!

[@rohandevaki4349]

great explaination

[@ItJunction4all]

Thanks a lot Rohan Devaki ! 😍 😍

[@mdaquib7836]

Thanks for your help. 2nd option Helped

[@ItJunction4all]

You're welcome Aqib !

[@Basireddy-li5mq]

Hi Sir, post we run these query still showing duplicate records in table------ delete from frds where frd_id not in (
select max(frd_id) from frds group by frd_name,dob,salary,dept_id);

[@chidinganyadi6796]

Thank you Sir. I have a question. What if the column names are many, will we start typing all of them in

[@ItJunction4all]

Yes, you will have to type all column names.

[@familybannas8809]

Love ur explanations

[@ItJunction4all]

Thanks a lot 😊

[@prashantsingh1892]

Sir using first method if i have different id name and rest of the records are same then it will not fetch as a duplicate values, So your first method is only for if the duplicate values are same if anything row number value is different it will not

[@ItJunction4all]

Thanks for writing Prashant ! First query will work if you have unique id for each row.
Let me know if this clarifies your doubt .

[@prashantsingh1892]

@@ItJunction4all got it sir.. Thankyou 😇

[@ItJunction4all]

@@prashantsingh1892 Welcome Prashant !

[@mohamednawfel5172]

this is really helpful

[@ItJunction4all]

Thank you !

[@push_freebird6693]

fabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
finally got a solution!!!!!!!
thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@ItJunction4all]

You are welcome ! I hope you will like other videos of my channel as well !

[@VeronicaAngryPolak]

could you write a case statement for duplicate rows and delete duplicates that way?

[@ItJunction4all]

I don't think it is possible to delete duplicate records using case statement

[@lokeshhousnoor3099]

Thanks Alot

[@ItJunction4all]

You're welcome Lokesh ! I hope you are liking my other SQL videos as well. Let me know your feedback ?

[@RajuSharma-j7x]

I Don't understand, why did you use Duplicate_CTE . Could you please explain.

[@chittibabu8149]

Same

[@malisachin469]

it's showing "ERROR 1288: The target table duplicate_CTE of the UPDATE is not updatable" in MYSQL. plz help me with this

[@hemaprajapati8866]

I am facing this same problem

[@user-id9yf2vm8v]

If this query will run in mysqlworkbench 8.0 in Mac laptop because its not running in my laptop..please help

[@ItJunction4all]

I think MySQL 8.0 supports these SQL queries

[@thebestken]

What if the unique id is not integer but a unique identifier like Guid? You can’t use max there. How will you do it?

[@ItJunction4all]

Thanks for writing Ken Singh ! Max function works on alphanumeric values as well . I think it will work on Guid as well.

[@MrSayandeep]

Thanks a lot

[@ItJunction4all]

Most welcome Sayandeep !

[@jagannathgaidhani5333]

sir, which platform are you using for this.
i am using CLI its very difficult there to execute big Query.

[@ItJunction4all]

I am using SQL Server.

[@hannahgetachew8909]

thank you

[@ItJunction4all]

You're welcome Hannah Getachew !

[@Sharmine_]

Thanks for the video. I am trying to use CTE and ROW_NUMBER(), however I got this error: The target table tableName_CTE of the DELETE is not updatable. Do you know how to resolve the error?

[@ItJunction4all]

Thank you Sharmine for writing ! Could you please tell me which database you are using ?

[@Sharmine_]

@@ItJunction4all I am using mySQL workbench

[@ItJunction4all]

Looks like delete from a CTE on MySQL 8+ is not working. It works good in Sql Server database. You can go for another approach for deleting duplicate records in mySQL database.

[@Sharmine_]

@@ItJunction4all I also tried the first DELETE method, the problem is it took a long time and will only display timeout

[@ItJunction4all]

@@Sharmine_ Ohh..I see. How many duplicates you are trying to delete from your table ?

[@rohitdeshbhratar6467]

Hi,
i am executing with clause query in Oracle PLSQL developer and i am getting error missing select keyword.
Please help me.

[@Ramya-o6m]

I'm facing the same issue. It seems it is application dependent and not working to delete cte records

[@Pkrish344]

[@ItJunction4all]

Thanks

[@prasadkumbhar1656]

Sir I have a doubt,
Suppose with the same table if there is no ID column but there are some other column as well say (deptno,deptname,loc, etc)which have duplicate records and the table does not have primary key column defined in this case how should we delete duplicate records where there are more no of columns??

[@ItJunction4all]

Hi Kumbhar...In that scenario, you can delete duplicate data by the way i have explained using CTE and Row_Number(). I hope this will help.

[@debashisahu]

@@ItJunction4all sir there also you have used order by id , so how to tackle that one

[@SanketBandi]

@@ItJunction4all SIR it is not taking Row_Number() as key word what should i do?

[@ItJunction4all]

@@SanketBandi Which database are you using ?

[@galazostephaniemae1919]

Sir how about in windows 10?

[@leecallaghan4625]

good day . i have an issue with my leads platform . when i enter a lead it says it already exists . i wa looking into this issue and found that it maybe an issue with a DUPLICATE KEY . does anyone have any idea .

[@behindthescenesreality9368]

Sir before making video please maintain visible screen size it's hardly visible to me may be it's only my problem or for other also don't know...

[@ItJunction4all]

Ok Sure. Thanks !

[@shambhum1829]

Sir great information but video is not proper visible screen size very small please can you make this same video one more time and upload

[@MAH-ls1if]

what is CTE?

[@ItJunction4all]

CTE is Common Table Expression

[@uma1910]

Are you continuing these questions?

[@ItJunction4all]

Yes I will continue adding questions as and when time permits me.

[@manishmishra6245]

8:21 using cte

[@ItJunction4all]

Thanks Manish Sharma !

[@vishnuvamshi4645]

NOT WRITTEN QUERY IN DESCRIPTION PLEASE ADD THE QUERY

[@ItJunction4all]

Vishnu..All queries are there in video.

[@manmathan0762]

Your method is not working for me bro is shows error: releation "duplicate_cte does not exist
Line 6: Delete from Duplicate_CTE where Dupont >1

[@ItJunction4all]

Which database are you working ?

[@manmathan0762]

Postgres bro I was new pls guide me

[@chittibabu8149]

What is duplicate cte

[@ItJunction4all]

CTE is a Common Table Expression. With the help of CTE , duplicate record was deleted

[@RijyoMagazine]

Video not clear

[@ItJunction4all]

Video is clear...open this video from UA-cam and go to settings and then click on Quality and select 1080pixel quality. Your video clarity will be equivalent to HD video

[@rizwanwaheed3073]

Thank you

[@ItJunction4all]

You're welcome Rizwan !