Select where rows exist more than once with variation

Home / Uncategorized / Select where rows exist more than once with variation

Question:
This is a hard one to put a title to so I apologise for ambiguity.

I have the following MySQL table (it’s a Magento table):id attr_id store_id entity_id value
—-+———+———+————+————————
1 45 0 173 Sale Gifts + Apartment
2 45 0 175 Sale Outerwear
3 45 1 175 Sale Outerwear
4 45 0 177 (null)
5 45 1 177 New Arrivals
6 45 0 178 New Tops
7 45 1 178 New Tops
As you can see, some of the rows have the same everything except store_id.

I want to do the following:
If a row with store_id = 0 has a duplicate row, but with store_id = 1 and different values (for example, rows 4 and 5 above), update the row with store_id = 0 to have the same value as the other.

Delete the row with store_id = 1

I know I will probably need a combination of both PHP and MySQL for this. I just don’t know what the MySQL query would be.

Any help would be great!

EDIT

The end goal from the above table is the following
id attr_id store_id entity_id value
—-+———+———+————+————————
1 45 0 173 Sale Gifts + Apartment
2 45 0 175 Sale Outerwear
4 45 0 177 New Arrivals
6 45 0 178 New Tops


Answer:
In order to retrive redundunt values having the same entity_id, you can do :
SELECT
*
FROM
magento m1, magento m2
WHERE
m1.attr_id=m2.attr_id
AND
m1.entity_id=m2.entity_id
AND
m1.id > m2.id
And for fixing null values, you will need to loop the above results and search for the null and replace it with the previous or next result.
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *