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:
*Instagram :

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

[yonashabtesllassie]

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

[tonygiovanni]

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

[SamuelLedk]

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

[rajeshSharma-odsn]

Nicely explained by you sir
Very helpful video
Thank you sir

[dhruvchaudhary_]

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

[masheTyMasha]

Thank you so much! Very loligicall explained.

[malisachin]

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

[Basireddy-limq]

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

[mdaquib]

Thanks for your help. 2nd option Helped

[leecallaghan]

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 .

[idyfvmv]

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

[VeronicaAngryPolak]

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

[vfdkegg]

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

[prashantsingh]

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

[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?

[chidinganyadi]

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

[rohitdeshbhratar]

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

[prasadkumbhar]

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??

[jagannathgaidhani]

sir, which platform are you using for this.

i am using CLI its very difficult there to execute big Query.

[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?