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

[@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)

[@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.

[@karthikd2104]

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

[@adapass]

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

[@marvinmachitar7229]

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

[@tchelaa6894]

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

[@MoodyVideoGraphies]

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

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

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

[@mastanshaik5479]

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

[@giridandi7480]

Very nice.simple understanding way you explain so nice

[@naveenshagala3136]

Nice explanation with such a calm voice.

[@crackconcepts]

Thank you 😃

[@sushantpawar5635]

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

[@Rjjithin777]

Simply superb, doing a great job, simple and clear

[@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 *

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

[@saravananbhojan8750]

Learned best way to query thanks for the video !!

[@rajattalnikar6167]

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

[@zafariqbalunknown9786]

Your efforts helped me a lot Thankyou mam

[@husler7424]

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

[@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.

[@deepakaggarwal8624]

Excellent, your solutions are just great and easy to understand

[@akashverma1640]

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

[@DeepakMeena-kz9bu]

over partition example was really helpful

[@ankiturmaliya7286]

very good explanation..

[@maryamfatima5882]

excellent explanation

[@boddanapalliavinash6418]

Very good explanation keep it up 👍

[@techninja9461]

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

[@aeriesx4727]

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

[@oic_Oracle_integration_cloud]

SO SIMPLE AND EASY

[@joshyjohn9636]

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

[@hadireg]

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

[@MUSKAN0896]

This was very helpful! Thank you so much

[@veerulinganagoudar3456]

It's really helpful

[@ganeshstars1]

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

[@nimmikcherian3242]

Very detailed description... thanks

[@umeshgolla2730]

Excellent explaination..

[@crackconcepts]

Thank you!

[@meditationandrelaxationmus741]

Thanks it is very important interview question

[@nausheen292]

Very well explained....Thank you 😀

[@shivyogi806]

You have amazing, simple.way of teaching. God bless you and Thank you very much

[@swapneelkambe2092]

Good explanation

[@Sriramlivz]

Nice Explaination. You have a lovely voice

[@rosonerri-faithful]

Can we use the MAX(ID) method?

[@mohansaivelagapudi929]

Thanks for Helping

[@leesubiramaniyam2704]

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

[@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.

[@MohanGonnabathula]

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.

[@shwetabudake6144]

Very well explained

[@namratanikam7566]

Well explained👍

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

[@sohailkat8867]

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

[@richardconnolly4295]

SUPER HELPFUL!

[@jaganjayakumar4874]

Clear explanation keep doing more videos👍

[@praveenvadde278]

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

[@ravindrabisht7372]

Your English is good

[@mudasar8629]

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

[@shubhamchoudhary5461]

So informative...👍

[@Aryan-ly5ho]

Helped me a lot Thanks 😁

[@shubhamanand1347]

your videos are awesome please make more videos on different topics

[@shankar8816]

Ek different positive way me le gye aap lecture ko.. Wldone

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

[@ravaliannaldas9320]

Nyc video sister.

[@akash_pal]

well Explained !! Thank you.

[@bro_7079]

kzbin.info/www/bejne/sHKcp2yio7VleKc

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

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

[@maxiejane]

So helpful thank you for sharing!

[@AmanKhan-ei4cg]

Thanks for such stuff🥰

[@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 );

[@vrcks8066]

ur voice is so nice😍

[@yes-pg5dy]

Excellent madam pls do videos on power bi also pls

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

[@norbycreation1851]

INSERT INTO temp_table SELECT DISTINCT * FROM old_table;

[@saurabhbenbanshi3460]

thanks you explained well...

[@YPTechDATIA]

use insert into newtable select distinct * from oldtable; then delete * from oldtable; now insert into oldtable select * from newtable;

[@mannu567]

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

[@labheshlalka9666]

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

[@vivekrahate2564]

Thank u dear..

[@CarlosRivera-mp7mm]

Excelente muchas gracias

[@alekhyabodapati5003]

where rn>1 is not a good practice use having rn> 1 instead of it. Good video

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

[@mangamanasvi5485]

Good Job!, Thank you.

[@kshathrugan5238]

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

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

[@aditiarjun519]

Hello mam, deleting using CTE throws the following error.... "Error Code: 1288. The target table cte of the DELETE is not updatable" how to resolve it?

[@mrrishiraj88]

Great videos.

[@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.

[@shubhamshingi4657]

Will 3rd and fourth approach delete row from original table or it will just delete from temp table/result sets ? can you please clear my doubt

[@galymzhankenesbekov2924]

i also had the same doubt, but decided to insert into temporary table first and then do operations in a temporary table instread of original DB

[@octotube463]

Both of them won’t impact the actual table. 4th method is just removing the duplicates from the temp table, you can achieve by using a distinct query 😂. My guess for the 3rd query is it will remove both the duplicate and the actual record.

[@qaz_testzaq_test8138]

Good work, thanks

[@dstar2211]

superb...good job...

[@crackconcepts]

Thank you!

[@88pianissimo90]

I think row_number( ) works slower ... right? If you have millions of records then this will work slow.

[@digitalcode5685]

THANKS

[@antojeanston4781]

it will be really helpful , if you do this on your system, please, it should be a live workout from your end, instead of explaining it on the written paper.

[@priyakumar3802]

She will explain the way she wants to explain.You should be thanking her instead of showing flaws

[@music4u1235]

so sNice tutorialt like that

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

[@rk-ej9ep]

Very nice

[@stoic2454]

any idea why a CTE is used when we can create temporary table using sub query.?

[@octotube463]

Again, changes in the CTe table wouldn’t impact the actual table. 3rd and 4th queries won’t work.

[@sathyasathyam6930]

Super thank 🌹you

[@HardikShah17]

Perfect. Thanks.

[@galymzhankenesbekov2924]

also i did this DECLARE @employee TABLE (emp_id INT IDENTITY PRIMARY KEY, last_name VARCHAR(30) NOT NULL, first_name VARCHAR(30) NOT NULL, job_title VARCHAR(50) NOT NULL) INSERT INTO @employee VALUES ('Smith', 'James', 'Staff Accountant'), ('Smith', 'James', 'Staff Accountant'), ('Williams', 'Roberta', 'Sr. Software Engineer'), ('Weinberg', 'Jeff', 'Human Resource Manger'), ('Franklin', 'Victoria', 'Operations Manager'), ('Armstrong', 'Williams', 'Database Administrator'), ('Cromley', 'Eric', 'Recruting Manager'), ('Smith', 'James', 'Staff Accountant'), ('Richardson','John', 'Safety Clerk'), ('Horton', 'Michelle','Accounting Manager'), ('Washington','Mark', 'HelpDesk Technician') select * from @employee; with CTE as( select e.*, ROW_NUMBER() over (partition by emp_id order by emp_id) as rn from @employee e ) delete from CTE where rn >1 also did not work

[@foolchandsonariwal4203]

Madam derived table delete allow nahi karta he only u can use cte to delete duplicate record.