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

wonderful explanation! I got overwhelmed!

Pls sent the cars file

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✌

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

1st 2 solutions are not working in MYSQL, You can't specify target table 'cars' for update in FROM clause --getting these error

Hi Bro,in solution 1 if three or four duplicate records then like this we can write-> delete from cars where id not in (select min(id) from cars group by model,brand having count(*) >1);

Very Useful toufiq

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,

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.

Scenario 2: Data duplicated based on ALL of the columns <<<<>>>>
alter table cars add column row_num int auto_increment primary key;

DELETE c1
FROM cars c1
JOIN cars c2 ON c1.model = c2.model AND c1.brand = c2.brand
WHERE c1.row_num > c2.row_num;

Hi ... From scenario 2 and sol 1 delete with ctid , if ther are multiple records duplicate .... Rather than it display all records ...it's only filtering max records ... Please help me on that

Thank you❤

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

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 ?

Thanks for this great video! I believe solution #2 will not work if you have 3 copies of the same row. Can you please check?
I.e., if the same model and car repeats 3 times with different ids, then your result will have 2 rows with different ids.
The 3rd solution will work, though!

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

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.

its not executing showing this (Error Code: 1093. You can't specify target table 'cars' for update in FROM clause)

Please make a video on advance sql questions.
Please.

Perfect explanition!!

Thanks😊

But the 1st solution only deletes the 1 duplicate which has max id. What if the count is > 2

Method 2 doesn't seem to work in Mysql. Raises the error "You can't use the same table in from clause". How can we solve this problem ? I see a few solutions that suggest self join instead of subquery. but couldn't understand the logic of how it is deleting from main table.

Using cte?

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

Happy Gurupornima 🙏

SECINARIO 2
--> USING ROW NUMBER FUNCTION

WITH CTE AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)RN FROM CARS)

DELETE FROM CTE
WHERE RN > 1

super explaination bro

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

Very helpful

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

Please makea video for data migration concept

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;

I have a query. in a table of 6 rows and 5 column I have added a new column(now 6 columns). So now to enter the data in that newly added column of all rows at a time what will be the query? could you please help me out with this?

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

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.

Hi, your solution1 is having a error it should be "not in" in the delete statement as your query will not work in the case where more than 2 similar records are found for the same model and brand.

is there any course in udemy (you have any membership in udemy)

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.

Sorry, why do you not use distinct?

select DISTINCT model, brand
from cars
order by model, brand

Thank you.

Hey
techTFQ,

Where i can learn Python , please help me on that ....

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.😍

if id column is primary column then

Not working with MySQL!!! :(

explanation op💙❤

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!

hi