How to Identify and Delete Duplicate Records in SQL | Easy Explanation with Real Time Scenarios

In this video we will discuss how to delete duplicate rows in SQL. We will discuss multiple scenarios and different ways to delete duplicate data.
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #duplicate #data

[@soumyagobbani5336]

Great explanation on different approaches of deleting duplicates. How to delete duplicates without creating backup table when there are duplicates with exact same rows. Below would give an error saying cte does not exist. with cte as (select *, row_number() over(partition by emp_id order by emp_id) rwn from employee) delete from cte where rwn > 1

[@PRASANNA170296]

cte is a temporary table, that you have created in this query, it doesnt have existence unlike employee table, thats why you cant delete records from it

[@avi8016]

Great video sir💯 Most of the time I have faced duplication issues with entire row So I used row_number to take care of that

[@ankitbansal6]

Thanks for sharing

[@ShreyaMishra0307__]

Great Video Sir !! Also, for SQL Server, the best approach would be Row_Number(), that will solve both the problems.

[@ankitbansal6]

Yes, you are right

[@souravsinha5330]

Thanks a lot greatly explained...

[@ankitbansal6]

Glad it was helpful!

[@khushishrivastava331]

Thank you for making such insightful videos on sql. Please advise some do's and don'ts in an interview or how to approach a problem statement.

[@ankitbansal6]

Noted

[@shubhamgoyal3358]

@@ankitbansal6 Instead of giving links of so many videos try to give the code script

[@Artouple]

Great approach!

[@ankitbansal6]

Glad it was helpful!

[@ankimedia5225]

Please provide optimised query for to detect duplicates when we will having million rows and want query to run fast

[@sudarsanrout5787]

Great video sir❤

[@ankitbansal6]

Keep watching

[@sarathmaya6083]

Nice explanation. May i know which IDE you are using?

[@ankitbansal6]

Thank you. It's datagrip.

[@sauravrajchaudhary9036]

Nice explnation

[@ankitbansal6]

Keep watching

[@sandipjamdade4564]

Thank You So So Much Ankit for putting in such great efforts....💯💯 But I have one Question How to delete pure duplicates without taking a backup of the table? Is It Possible?

[@ankitbansal6]

There is no standard way of doing it. Depending on databases it can be done.

[@yuktibhatnagar9955]

With cte as (Select*, row_number() over(partition by emp_id order by create_timestamp asc) as rank from employee a) Select emp_id, max(create_timestamp) From cte Group by emp_id;

[@tusharshivhare9651]

Hello sir, Can we use this approach also to remove multiple duplicates with cte as( select *, row_number() over(partition by emp_id order by timestamp desc) as rn from employee) delete from cte where rn>1

[@mayankgupta7548]

No you deleting from cte Cte are views only

[@akshayshirbhate7]

Hey, what about if i have duplicate record but its id is different. In that case how would we delete that record?

[@subhashdixit5167]

Please provide create table and insert statement like you used to provide in your odl videos

[@ankitbansal6]

There is nothing in the table. You can create table and data as per the video

[@patilnaveenkumarreddy4059]

thankyou

[@ankitbansal6]

You're welcome!

[@rahulgautam511]

Ankit, your videos are just fabulous my friend. I am using SQL Server, While deleting duplicate records with multiple columns I am getting an error ("An expression of non-boolean type specified in a context where a condition is expected, near ','). Is it because that ,SQL Server doesn't support multiple column subqueries? Please confirm. Thanks in advance..

[@ankitbansal6]

Yes it doesn't support multiple columns. You can concatenate the 2 columns and then use in filter

[@sahilummat8555]

Hello Ankit Just wanted a little help . How is the course different from all the videos you have put . What additional knowledge will we get ?

[@ankitbansal6]

The course is in a structured manner covers from basic to advanced all the concepts along with assignments. Also covers 2 projects and premium subscriptions to practice SQL problems.

[@sahilummat8555]

@@ankitbansal6 when will the next batch start and is live batch any different from the recorded one you currently have on the website

[@florincopaci6821]

MERGE INTO your_table tgt USING ( SELECT ROWID AS rid, name, ROW_NUMBER() OVER ( PARTITION BY name ORDER BY ROWNUM ) As rn FROM your_table ) src ON (src.rid = tgt.ROWID AND src.rn > 1) WHEN MATCHED THEN UPDATE SET name = name DELETE WHERE 1 = 1; Unfortunatlly this will not work in Sql Server or at last me i couldn t make it work but in Oracle work like a charm. Happy Holydays.

[@mbkamath3720]

Mind blowing🤯🤯🤯..! Why using the alias A when you havnt used it anywhere like A.empid or A.salary?? This doubt follows me evrywhere..😓😔😔

[@ankitbansal6]

In SQL server it's mandatory to give aliases to a sub query.

[@kundankumar5520]

Hi Sir, thanks again for wonderful video, I tried deleting in workbench platform but it is not allowing me to delete. I have uncheck the safe mode as well. Error: You can't specify target table 'employee_new' for update in FROM clause. Same query I am using it: delete from employee_new where (emp_id, create_timestamp) = (select emp_id, min(create_timestamp) as create_timestamp from employee_new group by 1 having count(1) >1);

[@zahoorahmadkareemi6267]

same issue with me

[@chennaivibes8428]

Hi can u provide the database you use so that it will be useful for practicing

[@ankitbansal6]

You can practice on any database. Just create the table and insert records

[@mbkamath3720]

Please give tips on not going blank during intrvws. The intrvwer acts hurry hurry and i end up saying 'i dont know' even though later i realise i knew the answer. So many opportunities missed due to this fear.😓😔😔😔

[@rishav144]

Maybe u need to practice more questions before Interview ...For hurry , You can tell interviewer to give u 1 minute to think ... Ask him " May i think for a minute " and try to recall the answer in ur mind... and then answer accordingly ....This worked for me in 6-7 interviews of Data Analyst

[@skillup638]

@@rishav144 hi rishav what are the Major skills required for data analyst and how to apply for companies ?

[@LoveIndia3]

i thought I am the only one who is facing this problem..i jumbled even after knowing the answers which make bad impression to interviewers which lead to rejection unnecessarily

[@muralidhar9266]

It's same with me also. knewing the right answer ,still can't recall at that moment, mind runs in hurry to impress or something else

[@mohdmahtab8991]

Please sir strat SQL courses from basic to Advance.

[@ankitbansal6]

Join here www.namastesql.com

[@mohdmahtab8991]

@@ankitbansal6 i checked it out but you don't list Normalization topic in your courses

[@paragkelkar8538]

I am getting this error "An expression of non-boolean type specified in a context where a condition is expected, near ','." after running query delete from EMP2 where (Emp_id,create_date) in (select Emp_id, min(create_Date) as time_c from EMP2 group by Emp_ID having count(1)>1) Please comment what needs to be update in this

[@ankitbansal6]

Is it SQL server?

[@paragkelkar8538]

@@ankitbansal6 Yes

[@ankitbansal6]

@@paragkelkar8538 ok so in SQL server you can not filter on 2 columns as tuple. Use this syntax using inner join DELETE w FROM WorkRecord2 w INNER JOIN Employee e ON EmployeeRun=EmployeeNo Do join on the 2 columns

[@sreemalapal]

Hi..Can you share the name of the application you are using..This is not MS SQL Server

[@ankitbansal6]

Datagrip

[@mohitupadhayay1439]

Not the best video in terms of video production but well explained a few things.

[@ankitbansal6]

Agree with you. Was trying a new IDE but it did not go well.

[@reachrishav]

Please continue the tutorials in SSMS, this editor does not seem to go well

[@ankitbansal6]

Ok. Thanks for the feedback 😊

[@chriskeo392]

Accent too heavy , thanks for content tho

[@girdhar3224]

somewhat confusing...

[@iamneeljadhav]

Good SQL problems but explanations not that good. Will love to watch if you speak in language you are comfartable to explain