CODEWITHSUNDEEP
phpmysqlsqlduplicates
Jan
Jan

Reputation: 397

SQL: Delete duplicated rows? (PHP)

I have the following database and want to delete the red ones because they are doubouled. So I have to check every row if another row is matching by pid, price, price_old, link and shop.

But how can I check that and how can I delete it then?

enter image description here

Maybe an easier way would be to generate a id from the values inside each row. So if the values inside a row would be equal also the id would be equal and who have only one value to compare with the other id's. Is that a better way? - If yes, how can I do that?

Greetings!

Upvotes: 1

Views: 3838

Answers (4)

Dwipam Katariya
Dwipam Katariya

Reputation: 144

select * from 
(select pid, price, price_old, link ,
row_number() over(partition by pid, price, price_old, link, shop order by pid) as rank
from my_table) temp
where temp.rank = 1

This Query will group by all the columns first and rank them. Duplicate rows will have rank > 1. It does not matter we take first or second row as both are copy of each other. We just take rows with rank 1. Rows that are not duplicate will also be having rank 1 and hence won't be neglected.

One more way to this is by using union.

select * from my_table UNION select * from my_table

Upvotes: 0

zed
zed

Reputation: 3267

Test this first on a test table:

DELETE t1 
FROM t t1, t t2 
WHERE t1.id > t2.id AND t1.price = t2.price 
    AND t1.link = t2.link AND t1.shop = t2.shop 
    AND t1.price_old = t2.price_old;

Basically you are removing the one with the highest ID if those parameters are equal

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Do the fact you have no way for get thi distinct row you could add uniqie id using 

ALTER TABLE my_table
 ADD id int NOT NULL AUTO_INCREMENT

Once done you could use not in where the id are not the min grouped by the value you need for define the duplication 

delete from my_table 
where id  NOT in ( select min(id) from my_table
                  group by shop, link 
                  )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

The simplest way is to run a distinct query:

select distinct pid, price, price_old, link, shop
from t;

You can create a new table using into. That is the simplest way. Because all columns are the same, MySQL doesn't offer a simple method to delete duplicate rows (while leaving one of them).

However, it is possible that your current results are generated by a query. If so, you can just add select distinct to the query. However, it would be better to fix the query so it doesn't generate duplicates. If this is the case, then ask another question with sample data, desired results (as text, not an image), and the query you are currently using.

Upvotes: 0

Related Questions