DELETE DUPLICATE ROWS FROM A TABLE IN SQL || 4 ways

In today's video, we'll learn how to delete duplicate rows from a table in SQL.
4 following ways are shown:
1) remove duplicate rows using a temporary table
2) remove duplicate rows using auto ID
3) remove duplicate rows using row_number()
4) remove duplicate rows using CTE
Thank you!
✨Work related mails can be sent on:
[e-mail]
✨If you appreciate my work and would like to donate to support our channel, you can do so here:
paypal.me/sadia1702

[@crackconcepts]

✨Link to my SQL e-book: imojo.in/rkajq9
✨Link to 'Switch your career easily' e-book: imojo.in/1FL7KaG
(Perfect for anyone willing to switch to a completely new field or to a different company in the Tech World)

[@karthikd2104]

Seen so many videos in SQL messed up ...this one really understandable with clear explanation!

[@rajkiranboggala9722]

I really appreciate your time and effort in sharing these (even though you are having cold)!! Thank you

[@srinivasprasad7765]

Ya true..👍

[@ravindrabisht7372]

That's called dedication...keep it up dear

[@ShubhamKumar-mf4sg]

Having cold ..hahaha..nailed it bro

[@tubepankajyou]

Such developers actually reach to top positions, appreciate it.

[@umaannamalai3205]

Thank you for the clear explanations! You made the concepts simple to understand. I’m excited for new videos.

[@akashagarwal6390]

Mam it would be great if you can take up topics or concepts which are really important like this though not much frequently asked in interviews and also it is useful for an engineer to know all these as well

[@marvinmachitar7229]

Wow! You are so good teacher. Was able to understand it more than other videos with fancy animation.

[@mastanshaik5479]

Nice & simple way of understanding 👏👏 for new starters in sql ur videos are best .. thank you for ur efforts and time

[@adapass]

explanation is very clear by writing in paper which is unique in youtube. great job

[@Rjjithin777]

Simply superb, doing a great job, simple and clear

[@rajattalnikar6167]

Thankyou so much. You've covered all the methods and discussed them in detail.

[@tchelaa6894]

I have been stuck on this since yesterday. Thank you😎

[@deepakaggarwal8624]

Excellent, your solutions are just great and easy to understand

[@bhavanikandikondamatam5926]

Hello madam..
I have doubt in using temporary table..
Can insert distinct values in new table
Drop the old table
Rename new table as old table?

[@aeriesx4727]

Absolutely brilliant explanation... Thank you so much..

[@MoodyVideoGraphies]

kitni awesome hai yaar... So well explained... calm and soothing...

[@techninja9461]

Excellent Explanation.....This video is really helpful.

[@MUSKAN0896]

This was very helpful! Thank you so much

[@maxiejane]

So helpful thank you for sharing!

[@rajendrarajendra583]

Your explanation is superb. one small request what ever you explained can you explain in sql server . that is very use full to us.

[@sohailkat8867]

Nice video, and it would be great if you could make a video on types of Analytical Functions in sql, thanks!

[@nimmikcherian3242]

Very detailed description... thanks

[@rakeshgopidi4066]

It's a great post Sadia, no matter how many years of experience we have ,it's always a good revision for any one
# Additional Reference
There are also other ways to restrict the duplicates
1. UNIQUE
2. UNION
3. ROW_NUMBER | RANK () | DENSE_RANK () - Analytical Function
4. INTERSECT
5. MINUS
6. ROWID | ROWID + CORRELATED SUBQUERY
7. GROUP BY

[@nausheen292]

Very well explained....Thank you 😀

[@Sriramlivz]

Nice Explaination. You have a lovely voice

[@richardconnolly4295]

SUPER HELPFUL!

[@mudasar8629]

Thank a lots !!! :) Hope to see more videos from you.

[@giridandi7480]

Very nice.simple understanding way you explain so nice

[@akashverma1640]

Thank you very much for you efforts... Must to be known things👌👌

[@jaganjayakumar4874]

Clear explanation keep doing more videos👍

[@joshyjohn9636]

Well explained, It will be most useful video in this topic..

[@naveenshagala3136]

Nice explanation with such a calm voice.

[@crackconcepts]

Thank you 😃

[@Aryan-ly5ho]

Helped me a lot Thanks 😁

[@saravananbhojan8750]

Learned best way to query thanks for the video !!

[@mangamanasvi5485]

Good Job!, Thank you.

[@akash_pal]

well Explained !! Thank you.

[@bro_7079]

th-cam.com/video/zA9v9lnP2BE/w-d-xo.html

[@mohansaivelagapudi929]

Thanks for Helping

[@DeepakMeena-kz9bu]

over partition example was really helpful

[@maryamfatima5882]

excellent explanation

[@veerulinganagoudar3456]

It's really helpful

[@ankiturmaliya7286]

very good explanation..

[@saurabhbenbanshi3460]

thanks you explained well...

[@kshathrugan5238]

The first method ,the same query will it work in Oracle sql

[@tushardeepsingh4976]

Hi..one question i have ..pls rply ASAP... I'm using MYSQL and MYSQL doesn't support select * into.. therefore i used insert into newtable select distinct * from old table ...here i faced one issue...i hv one tble with eid,ename and salary. eid field is PK and ename and salary has duplicate values ...now when i use insert into using distinct keyword it copies duplicate data as well ......i only want distict data to be copied ..i tried dropping PK but on column eid but i'm facing same issue

[@husler7424]

Watching in 2022, for placements as being CSE student. Thanks a lot, didi. Your explanation is just amazing.

[@boddanapalliavinash6418]

Very good explanation keep it up 👍

[@praveenvadde278]

Good explanation. Can please explain and show by executing in the system.

[@shubhamanand1347]

your videos are awesome please make more videos on different topics

[@AmanKhan-ei4cg]

Thanks for such stuff🥰

[@zafariqbalunknown9786]

Your efforts helped me a lot Thankyou mam

[@swapneelkambe2092]

Good explanation

[@sheikhmohduzair4641]

Your contents are amazing

[@HardikShah17]

Perfect. Thanks.

[@qaz_testzaq_test8138]

Good work, thanks

[@shubhamchoudhary5461]

So informative...👍

[@shwetabudake6144]

Very well explained

[@namratanikam7566]

Well explained👍

[@tushardeepsingh4976]

Hii.. Please clear one question in this video ..In CTE inner query ..you have not used * after select keyword ..you have directly written " select row_number()..." .... * aaega na after select ??

[@faizazhar768]

SO SIMPLE AND EASY

[@azharuddinluhar9667]

3rd and 4th way will delete duplicate row from result set and not from actual table, now what to do for deleting duplicate rows from actual actual?

[@mrf7283]

very good effort and I appreciate your clips. However I have a question; suppose application messed up and gave same ID to two (2) different unique valid name. eg. 5001 to ABC and 5001 to XYZ and we want to keep both that is they should exists in our resultant with 2 unique IDs. Please don't get me wrong I am not trying to trick you, I had this real situation once with thousands of rows and was stuck.

[@user-rr9lo4wc8g]

In that scenario, you need to add ID, name in partition.
DELETE FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY ID) AS Rno
FROM emp
) AS E
WHERE Rno > 1;
This query will delete duplicate rows from the "emp" table where duplicates are determined by the combination of "ID" and "NAME" columns, keeping only the first occurrence and removing the rest.

[@meditationandrelaxationmus741]

Thanks it is very important interview question

[@CarlosRivera-mp7mm]

Excelente muchas gracias

[@saisumanthnagasuri7741]

What happen if there are more than 50 columns with the duplicate records
Instead of passing all the columns names to the partition by clause
Is there any way to put * .
Any thoughts?

[@sukritsen9530]

Hello, I have a question, how to delete duplicate records with no unique key in mysql? Can this be done using the row_number function?

[@chanduch8973]

DELETE K FROM
(SELECT *, ROW_NUMBER () OVER (PARTITION BY emp_id ORDER BY emp_id) AS RN FROM MyTable) K
WHERE RN>1

[@hadireg]

a bit speedy but you're doing a great job!

[@sushantpawar5635]

शेंबडाचा त्रास होत असताना देखील खूप छान शिकवलंस🙏👍 thank you

[@hidden590]

First method is easy to understand ...so I will go with the first method

[@FitVisionTV]

First method will overload database performance. Avoid using select *

[@ybujji]

Thank you so much

[@vivekrahate2564]

Thank u dear..

[@mrrishiraj88]

Great videos.

[@rosonerri-faithful]

Can we use the MAX(ID) method?

[@digitalcode5685]

THANKS

[@muraliraj9658]

Thank you.

[@ravaliannaldas9320]

Nyc video sister.

[@nareshd4199]

Thank you

[@ganeshstars1]

your explanation is good. I am watching most of the videios.
for me 1 st method is showing error missing expression.

[@vrocks2210]

ur voice is so nice😍

[@dhineshrajendran2312]

Much appreciated

[@Pavankumar71036]

Excellent

[@dstar2211]

superb...good job...

[@crackconcepts]

Thank you!

[@ramchinthakayala2092]

How come only distinct values will stored in new table if use -- Insert into old_table select * from new _table
? pls explain

[@yashgoswami5374]

because than new table has distinct values

[@dreambig1668]

Hi just one suggestion you first way will always not would the solution for routine. N in your second way we have to design table with identify type then you will be able to access

[@arjunpawar4564]

In third method : Can we access alias rn in outer query as well as use in where condition ?

[@Pooja-pd1ee]

It won't work.

[@sandysahu5651]

The row_number() methodology is not working in snowflake db . anyone help me on this, how to resolve this like if we have to mention some alias name aslo and instead of rn>1 if we have to write alias.rn>1

[@sathyasathyam6930]

Super thank 🌹you

[@ankittiwari-pw6nr]

Thanks

[@leesubiramaniyam2704]

Explaining good & may be increase the volume (sound) better good

[@simranjeetsingh4101]

Thanku

[@music4u1235]

so sNice tutorialt like that

[@joshuasamuel5851]

Superb

[@labheshlalka9666]

Why we can't use direct delete query whose count greater than 1

[@yes-pg5dy]

Excellent madam pls do videos on power bi also pls

[@rk-ej9ep]

Very nice

[@divyasnarayan1803]

Gr8 wrk!

[@mannu567]

Instead of MIN if we use Max then what will happen? I guess it will also remove duplicate

[@mohammedyousuf1918]

Super

[@MrsBestYT]

Good

[@helmiboubaker2742]

sorry but the third ways not working for me . this the query that i use
delete from
(select nom, row_number() over(partition by nom order by nom ) as count_num from fourniseurs)as tab where count_num >1;
but the select query work fine i wander why is it not working i use workbench mysql :/

[@mobileminimaster3015]

delete from fourniseurs where nom IN (select nom from (select nom,row_number() over(partition by nom) as count_num from fourniseurs) ee where ee.count_num>1 );

[@Almay20]

thank you so much! I followed the first step and it made a new table with the distinct values in there. Could we just leave it there without having to delete old table data and bring the distinct values back from new table into oldtable?

[@nihar4713]

yes it is done so that we don't have an empty original table which in business scenarios will be bad as there might be lot of dependencies in that table

[@ravindrabisht7372]

Your English is good