How to remove Duplicate Data in SQL | SQL Query to remove duplicate

In this video, we see 10 different ways to remove duplicate records in SQL. We look at 2 different scenario for duplicate records in a table and then come up with 10 SQL queries to remove these duplicate data from the database.
Data can be consider as duplicate if all column values are duplicated or if only some of the column values are duplicated. In this video, you will get solution to remove duplicate data for both these scenarios.
The dataset, scripts and SQL Queries used in this video can be downloaded from below link:
techtfq.com/blog/how-to-remov...
Have an SQL Query? Would you want me to make a video on it? Then do email me your query to [e-mail];
Timestamp:
00:00 Intro about duplicate data in SQL
01:22 Explaining Scenario 1 of Duplicate Data
03:02 SOLUTION 1 - Delete using Unique Identifier.
07:19 SOLUTION 2 - Using SELF join.
12:21 SOLUTION 3 - Using Window function.
15:32 SOLUTION 4 - Using MIN function. This delete even multiple duplicate records.
19:34 SOLUTION 5 - Using backup table.
24:10 SOLUTION 6 - Using backup table without dropping the original table.
26:06 Explaining Scenario 2 of Duplicate Data
27:02 SOLUTION 7 - Delete using CTID.
30:25 SOLUTION 8 - By creating a temporary unique id column.
33:38 SOLUTION 9 - By creating a backup table.
35:30 SOLUTION 10 - By creating a backup table without dropping the original table.
🔴 My Recommended Courses:
✅ Learn SQL: learnsql.com/?ref=thoufiqmoha...
✅ Practice SQL Queries: www.stratascratch.com/?via=te...
✅ Learn PowerBI: codebasics.io/courses/power-b...
✅ Learn Python: codebasics.io/courses/python-...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq

[@KetakiGadgil_15]

For the first solution, instead of deleting records from table and repopulating table again with those records, we can use:
select * from car
where id not in
(
select max(id) from car
group by model,brand
having count(*)>1
)

[@bollaalekyadevi6795]

It's blessing that i found your channel.. your way of teaching SQL concepts is really awesome. I am learning all SQL concepts from your videos which is really giving me confidence in queries. Thank you so much sir.

[@NawazKhan-go3fj]

Man you've explained exactly what i needed to know. You've no idea how relieved i feel after finally understanding the concept of joins. I needed to understand how sql works with each record of each table and you've explained that really well. Thanks a lot brother for this video. I'll definitely subscribe to your channel.

[@MASC_RAMBO4Fuggi]

You took this class to heaven, hats off.

[@swamivivekananda-cyclonicm8781]

Superb, best part of the video is distinct method when only few values are duplicate and when entire row is duplicate. Thank you so much.

[@mlevvy96]

I believe solution 8 has a "bug", if you would have more than 1 duplicate - that is 3 or more records with the same values you would just delete one duplicate with proposed query which has biggest id value. So with simplified schema = (id, model, brand, row_num) and records = [(1, X5, BMW, 1), (1, X5, BMW, 2), (1, X5, BMW, 3)] you would just delete last record and still have [(1, X5, BMW, 1), (1, X5, BMW, 2)] left. It can be fixed by changing the query to `DELETE FROM prod WHERE row_num NOT IN (SELECT MIN(row_num) min_id FROM prod GROUP BY model, brand HAVING COUNT(*) > 1)`
This way you would delete every possible duplicate. Anyway, great video✌

[@Mayank-jw9yy]

One of the best lecture ever I have seen in my life over SQL topic, so much precise clarity, in-depth knowledge & thoroughly discussion over a topic.....amazing...just amazing buddy.

[@dhruvshahvlogs]

This video is very practical and helpful when it comes to the real-time database. Thank you so much 👌👌👍👍❤❤

[@abdulsamadibrahim4929]

Thanks TFQ, I've been battling on how to remove duplicates from a project I am working on currently. But thanks to this video for coming to my rescue. Personally, I prefer the one of creating a temporary unique id column because we are used to having unique IDs in our dataset. The dataset I am working doesn't have a unique id, that's why it has been a bit difficult for me. But thanks once again.

[@sudeep2580]

This is the best video "How to remove the duplicate records from the table."

[@ArifRaza]

wonderful explanation! I got overwhelmed!

[@sivaramakrishnareddy]

Excellent teaching bro... very simple methods you have collected and explained 👏👍👌

[@NB20079]

I'm from Philippines and your new subscriber, I appreciate your lesson how you to discuss this in nice way and simple ,so that the viewer like me can understand clearly, thank you sir!

[@vishalsonawane.8905]

Your SQL content likes God gifted❣️ its same question asks in interviewer.🏆

[@tomjoe3380]

Very informative. Thank you so much for this video.

[@souradeep.chatterjee]

Thanks bro 🥳 need more interview questions like this in SQL.

[@user-tr9ot4yo6i]

Everyone can understand your video from school boy to graduate guy
Thanks a lot

[@saritaxsingh]

Thanks alot. You are helping me understand my current role problem.

[@faizmujawar3504]

Very helpful. Thanks a lot for creating such beneficial videos.

[@RandomlyWisdom]

Solution 3 can also work for Scenario 2 just need to give order by clause in over( ).
Thanks for the other solutions : )

[@vk-jk2gz]

Ur teaching by using examples is very easy to understand me thank u so much sir

[@ABaumstumpf]

I had the harder version of that recently:
Keep a configurable number of history-entries for each unique car-model - and also only keep those entries for a configurable amount of time.
The time-constraint is easy, keeping the newest N-entries is still easy, the problem is if that has to scale for performance.
The primary is a combination of sequence (foreign key to a table that contains timestamp-information) + item-ID (in your example car-model). And for each car i want to keep the newest 5 Entries where newest is the highest sequence.
Right now we are keeping ~10 000 sequence entries and 5 entries per item-ID - and it can happen that we add as many as 10 000 entries every 10 seconds.
I have tried nearly every one of the solutions you showed here already, the combination of rank+row-id is currently the fastest, but still takes ~600 ms for 2000 rows. it kinda feels like that should be possible an order of magnitude faster.

[@suyash7450]

You are doing a great Job and your way of explaining is also very good, Thanks for helping us out

[@Unknowing0011]

Thankyou so much for this awesome tutorial. It was soo effective and to the point.

[@viveknanhe1786]

Thank you so much sir, it helps me a lot in my data engineering interviews.

[@sahilbhange]

To remove row level duplicates, we can simply use set operation UNION
create table cars_bk as
select * from cars
UNION
select * from cars where 1=2;

[@F_A_R_man]

Oh, thank you so much. Your videos are really helpful. Thanks for your efforts.

[@nc193]

You explained that very well... Great help.

[@RavikaUniverse.]

Splendid and most Interesting solutions given hatsoff

[@dhemanth7437]

Hi tofique thanks for the video and also in scenario 2 we can also use "select * from cars union select * from cars" this also removes duplicates.

[@nagarapusudheeksha5750]

Your video is really help full for a data analyst who is struggling thank you so much ❤️👍🇮🇳

[@techTFQ]

Glad to hear that

[@snehalvaidya5843]

Happy Teachers Day Sir🎇 (5th Sep 22)

[@techTFQ]

Thank you so much Snehal 🙏🏼☺️

[@somasekharkunka8820]

I completely learning SQL tips from your channel sir. Thanks for your efforts and it is amazing..

[@techTFQ]

Glad to hear that

[@dhanrajpatil218]

Thank You Sir!!!! Your all explanations are just awesome.

[@techTFQ]

Glad you liked it Dhanraj 🙏🏼

[@goedzo4361]

this content is so good and helpful!

[@codingfox]

Solution 4 is the best, thank you! :)

[@michaely5375]

Thank you so much. Your lesson is so help full! I wonder do you have any lesson for preventing to have duplicate data in tables ?

[@user-de8gj5xi7f]

Thank you for your wonderful video it helped me a lot!!!

[@gowtham7819]

Thanks bro for the excellent explanation.kindly do video on index in sql

[@user-ny8rd8eg2v]

Thanks a lot brother. Its gonna help me in so many ways. Subscribed to your channel and ill definitely going to learn lot more things from you without any doubt. Thanks again

[@krantikumar894]

Superb bro ur explanation is awsome

[@adeebanaaz5855]

thank you so much..This helped me a lot.

[@abhishekgowda1776]

Thanks for making video on another helpful concept 👍please make videos in PLSQL concepts

[@shahrukhsultan8727]

Solution 3 and 4 are the best.

[@mohanakrishnanr1648]

Great Content bro Though fast very useful ..

[@leoprabhakar4166]

Very Useful toufiq

[@nitinmishra5027]

Perfect explanition!!

[@1112GT]

Hi Thanks for the contents. One request, can you please provide the query to highlight duplicates instead of deleting?

[@jamesopoku8049]

Wonderful presentation, I really like your presentation, thank you very much sir, infact u are making a positive impact in my life , keep it up

[@techTFQ]

I am so glad this helps.. thank you James 🙏🏼

[@singh9661]

Hey
techTFQ,Thank you so for your all videos, This is very helpful not helpful i think this is awesome for me, not me i think everyone , god will blessing you always.😍

[@netlifetoday5193]

Amazing instruction! Thank you.

[@techTFQ]

Glad it was helpful

[@arunrajv3080]

Bro, you are the best 🙌🤝

[@nageshmungikar5386]

Nice KT Transfer. Big Fan Of ur Channel Please Keep Posting Good work 💯💯

[@techTFQ]

Thank you so much :)

[@prithiviraj4424]

Thank you so much Thoufiq!!! ☺☺

[@chellas6784]

Very nice explanation

[@NitinKumar-ow5yr]

Your video is really help full for a data analyst who is struggling thank you so much ❤❤. how to delete in MYSQL server which code we can use in MYSQL.

[@jeffrey6124]

Great video! 😍You were able to touch based on most of the common scenario related to duplicate data in sql. Do you also have a playlist regarding Data Quality Concepts/Issues/Resolutions in SQL Server? If not that would also be a great video to work on and will be looking forward it 🙂

[@MuhammadSalmanKhan-lp4mf]

Assalam O Alaikum Toufeeq!
On 7:01 I do not think that it will remove all duplicates from table. In your case duplicates were maximum of 2 but Lets say that In some table we have duplicates more than 2 rows than i.e 4 rows are duplicates, then It will remove the row with maximum id and we will be left with 3 rows. Maybe I am wrong, so could you guide me about it.
Btw, I have learned a lot from your channel and I have watched almost all basics and intermediate playlist. Thank you so much.

[@neerajgupta6858]

You just have to use "not in" in delete query instead of "in"

[@rajatsethi6000]

Hey Thanks for your video
We can solve scenario two with below 2approaches also
Select * from cars group by 1,2,3,4,5
It will give you unique records
And second approach is using row_number by passing all variables in partition by
The above 2 are mostly use approachs
May be you find it better.
By the way I have learnt about window functions from your channel only.
Keep up the great work

[@techTFQ]

thank you and yes there are be atleast 20 different ways to delete dup records. these were just the 10 i could think off at the moment

[@ManiKandan-kg5ky]

Hi how are you doing?

[@avinash8535]

​@@techTFQ Sir, Could you please let us share your thoughts -
Along with SQL, What else can we learn like Power BI, SSIS and etc to get more opportunities in future.
BTW, UNION would also be preferrable as best option to remove duplicates

[@vebhs9769]

Woww...amazing video 💕

[@s.s.vinodhvinu9698]

Very nice video and explanation and would like to share the backup table creation as (Select *
into cars_bkp
From cars
where 1=2;) please check and share your feedback it works for me. Thanks for your video sir,

[@reddaiahreddymallu]

Thank you for the content.

[@mirageman2]

Great video, thanks a lot :)

[@abhaikumar10]

Complicating simple things just for the shake of video for creating 10 solutions

[@Aubd417]

your videos are really helpful opening mind to approach problem with different and effective way,,
share some session on hierarchical sql quires.
if its already available, share the link.

[@jesuszelayac]

Great tutorial !!

[@beks5755]

@techTFQ
Your first method 'Delete using Unique Identifier' works if we have just one duplicate but what if we have 55 rows with Tesla Model S?

[@user-hk4sv1wx6z]

super explaination bro

[@Imranbof]

Very helpful

[@dhemanth7437]

Hi Tofique a big fan of your lectures and just wanna mention that for solution 1 if have have more than 2 duplicates and we are deleting only the max one we will still be left with duplicates right.

[@YogeshKumar-me9es]

I think in self join we can also use this condition c1.idc2.id.

[@RamsEarner]

Thank you Tfq..., And I mean it.

[@ishratalikhan7528]

really very nice and very informative.

[@techTFQ]

Thank you 🙏🏼

[@cj7334]

Howdy, great work overall, however Solution 8 is not possible with Microsoft TSQL server as the Identity property can only be applied when the table is created and cannot be added in later through using an Alter statement. If you want unique identifiers on the data set you have to use Row_number windows function. Try this CTE query:
;with row_delete as
(
select id, model, brand, (row_number() over(partition by model, brand order by id asc) ) as row_num
FROM cars
)
delete from row_delete where row_num > 1

[@KrishRohith2000]

You made this easy for SQL Server users

[@kamyabsayed2164]

Hi Thoufiq,
Solution 1 for the duplicate data with ID can be achieved in MS-SQL with this query -> DELETE FROM cars WHERE id NOT in (
select id FROM
(
select id, row_number() OVER(order by id) as unid
from cars
group by model, brand
)
);

[@vanmathiraj2617]

I really fell in love with channel to build my career

[@techTFQ]

Thank you so much Vanmathi 😀

[@matthewwalton450]

If you are using SQLite you can use a NOT IN clause with built in rowid.
DELETE FROM dataset
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM dataset
GROUP BY col1, col2, col3 etc etc.
);
This will assign all duplicates a rowid, grouped by the columns you want to scrutinize, then it will delete all but the MIN rowid in that set of duplicates.
Hope this helps with SQLite users.

[@gecarter53]

Great solutions, easy to follow. BTW: If I had a table with duplicates, where the duplicates are records with numerous multiple column values (let's say records are duplicates if they matching values across 20 columns). Is it possible to write a query without list all 20 columns?

[@techTFQ]

thank you and I think you need to mention all column names.

[@upmdosadno]

In second solution could you not use one more condition on join. ie c1.id c2.id or even c1.id > c2.id

[@techTFQ]

c1.id > c2.id would work but then in select clause u need to fetch column from c1 table.
one does not work in this case. u can try it out

[@upmdosadno]

@@techTFQ i mean you can use c1.id > c2.id or c1.id < c2.id in this case you would just get different results in this case 1,4 or 5,6 it does not matter which one you delete.
Yeah wont work in this case. But it will get you all duplicated records so you will see 1,4,5,6

[@hilarylomotey7051]

Hey my favorite friend on the net. Awesome tutorial as always. When I come to India Pizza is on me for a whole week. Great video excellent explanation

[@techTFQ]

Haha thank you Hilary ☺️
Glad you liked it

[@hasanmougharbel8030]

Hey there, god bless your efforts.
Glad to meet again with a general enquiry.
Is "where" and "having" clauses similarly applied in sql?
Thanks for taking care of this.

[@prem486]

The basic difference which you should consider Hasan is that Having is used with agg functions such as count, max, min, etc. while “Where” cannot be used with agg functions and thats the main purpose of why Having was included in later parts of SQL.

[@akiemcameron5303]

The window function is definetly the best one for me. Is there a rule that governs which method you choose based on the problem?

[@nadinfridburg7088]

Thank you!!!!

[@ebj5805]

For the 2nd solution would this where clause work the same? I'm using data without unique ids:
WHERE c1.color c2>color

[@akshayhalande8763]

thank you 🙌

[@kavithabalasubramaniyan3390]

Thank you❤

[@Alltec2233]

Good info

[@abhijitcaps]

We can also use row_number() and dense_rank() for all column(s) duplicate row.

[@shivanipurohit2875]

In the first solution if there were 3 duplicate record of same group, wouldn't 2 of them remain if we delete the record with max(id)??

[@krishnamangalapally2314]

Thanks😊

[@jacobreddydevidi1305]

@techTFQ delete from cars where model in (select b.* from cars a
join cars b on a.model = b.model and a.brand = b.brand
where a.id

[@girdhar3224]

explanation op💙❤

[@Genial_Gouthi]

I started watching your videos and very clear explanation,planning to watch all the videos. Please make a video on join using multiple tables and how the data is populated from multiple table.

[@sarthakhaldar9107]

7 mins: This deletion will not work in mySQL. In MySQL, you can't modify the same table which you use in the SELECT part. Else you get error: ERROR: You can't specify target table 'cars' for update in FROM clause.

[@savichopra9083]

I have the same error

[@saharshtiwari5557]

Run this before delete query
Set sql_safe_updates=0

[@SuperMukeshjha]

Create your sub query with alias under main sub query

[@bhaveshverma8629]

Very very awesome 👍👍👍👍👍👍👍👍

[@techTFQ]

Thank you so much 😀

[@MAMAKIJAANGUGU]

Great solutions. Im not able to delete from the target table. I get an error message so I have to create a copy of the table.

[@kachvejathin]

Hi sir, instead of using generated always as identity we could have used ROWNUNBER or other window functions right..?

[@DatNguyen-lq9lf]

I think in the row_number() solution, you should have the order by id ASC in the Partition By. That is to ensure you will delete the newest id, not the oldest id

[@techTFQ]

I could but this was not a requirement to delete only the newest record.

[@DatNguyen-lq9lf]

@@techTFQ I see. As you use the Min in the other approach, I assume that you would want to keep the oldest record. Thanks for a great video, it’s very helpful

[@bhaskar9781]

My task was to keep latest based on timestamp so I have used row number

[@ganeshpadala3566]

@@techTFQhi team, I have one query can I talk to you

[@DE-Py-Sq-Az-Db]

Thank You!!!

[@techTFQ]

You're welcome!

[@anmolsonthalia7371]

May I know which tool you use this SQL to analyze or interpret it?