Remove row if column contains duplicate data SQL

Question

My goal is to delete/remove any rows from the ma_images table which contains a duplicate of the same piece of data in anther row, but to retain the original/first instance of that data which has been duplicated. To put it another way, if row #1 mentions 'image_1.jpg', and then rows 3, 5, & 6 also mention the dame data imageNameMaster column, i want to delete instances 3, 5, & 6 but keep the first instance (#1)'

I'm not good with sql and have been working on this for a day now, googled, read stack, researched, haven't found an example that i can understand/relate to my problem to effect a solution.

Below is an example of the table i'm working with minus some of the extra columns in the table. Below that is the lastest bit of sql i've tried so far and the error message which that attempt produced.

example of table minus a lot of columns

ERROR: You can't specify target table 'img' for update in FROM clause

delete img
from  ma_images AS img
where exists (
    select 1
    from ma_images ref
    where ref.ImgNameMaster = img.ImgNameMaster

        and ref.ImgID < img.ImgID
)

Gordon Linoff · Accepted Answer · 2018-09-22 17:34:31Z

2

MySQL is finicky about using the table being updated/deleted in a subquery.

The best solution is to use join:

delete img
    from ma_images img JOIN
         ma_images ref
         on ref.ImgNameMaster = img.ImgNameMaster and    
            ref.ImgID < img.ImgID;

This version might attempt to delete the same row multiple times. So, I would suggest:

delete img
    from ma_images img JOIN
         (select ref.ImgNameMaster, MIN(ImgId) as min_ImgId
          from ma_images ref
          group by ref.ImgNameMaster
         ) ref
         on ref.ImgNameMaster = img.ImgNameMaster and    
            img.ImgID > ref.min_ImgID;

answered Sep 22, 2018 at 17:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Monkeework Over a year ago

I was thinking it might be necessary, better to use a join, but joins confuse me so so sooooooooo much. i'm planning to tackle joins and classes in 2019 as I continue to learn php for fun (I'm a barrista, just doing this all for fun :D ) Thanks for the suggestions and I will try them

Monkeework Over a year ago

Also this appears to have worked -- and so so so fast... Can i have your baby? Thanks very much !

Collectives™ on Stack Overflow

Remove row if column contains duplicate data SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related