compare two values in sql with eachother

Question

I have a database of magento with double images, I want to delete those but first i got to detect them with a sql query.

I have tried this code

select t1.VALUE from catalog_product_entity_media_gallery t1 
join catalog_product_entity_media_gallery t2 on (t1.value = t2.value)

this one:

  select * from catalog_product_entity_media_gallery where value=value

and this one:

select
*
from
(
    select
        value
    from
        catalog_product_entity_media_gallery
    group by
        value
    having count(*) > 1
) as t
inner join catalog_product_entity_media_gallery on (
    catalog_product_entity_media_gallery.value = t.value
)

the first gives an error and the second- and third one gives back every product.

this is my small test database

What is the schema of the table catalog_product_entity_media_gallery? Is it the last picture? — mxix, Aug 29 '14 at 13:15

mxix · Answer 1 · 2014-09-02T08:54:03.620

0

Give this one a try:

select
*
from (
    select 
        entity_id,attribute_id,value,
        MIN(value_id) value_id
    from catalog_product_entity_media_gallery
    group by
        entity_id,attribute_id,value
    having COUNT(*) > 1
) A1
inner join catalog_product_entity_media_gallery A2 on
    A1.entity_id = A2.entity_id and
    A1.attribute_id = A2.attribute_id and
    A1.value = A2.value and
    A1.value_id = A2.value_id

edited Sep 02 '14 at 08:54

answered Aug 29 '14 at 13:18

mxix

3,539
1
16
23

it doesnt give back any results – Djeroen Aug 29 '14 at 13:46
congrats all the value_id have different values. if not true please supply more sample data. – mxix Aug 29 '14 at 13:51
this is all the data, i made a test database with only two products, one product with the entity_id:4 and one with 5, all the images have different value_id's – Djeroen Aug 29 '14 at 14:00
so... with that sample data you want to delete row with value_id: 5? – mxix Aug 29 '14 at 14:51
could you try the second query i supplied – mxix Aug 29 '14 at 21:07
it gives me this error: FUNCTION ROW_NUMBER does not exist – Djeroen Sep 01 '14 at 09:34
What SqlServer version are you running? – mxix Sep 01 '14 at 14:03
i am using phpmyadmin on a wamp environment – Djeroen Sep 02 '14 at 07:29
then you are running MySql and not Microsoft Sql-Server. Wrong tag. – mxix Sep 02 '14 at 08:28

score 0 · Answer 2 · answered Sep 02 '14 at 08:59

You can just get the min id by value, then except the other records:

select
*
from catalog_product_entity_media_gallery t1
where exists
( select * from 
  (select  value, min(value_id) as min_value_id
    from   catalog_product_entity_media_gallery
    group by  value
  ) as t2
  where t1.value=t2.value and t1.value_id=t2.min_value_id
)

If you want delete the duplicated rows, change exists to not exists.

delete
from catalog_product_entity_media_gallery t1
where not exists
( select * from 
  (select  value, min(value_id) as min_value_id
    from   catalog_product_entity_media_gallery
    group by  value
  ) as t2
  where t1.value=t2.value and t1.value_id=t2.min_value_id
)

compare two values in sql with eachother

2 Answers2