removing duplicate row from mysql where value equals something

Question

I've all the way to the end of the internet and I'm proper stuck. Whilst I can find partial answer I'm unable to modify it to make it work.

I have a table named myfetcher like:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| fid_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| linksetid   | varchar(200) | NO   |     | NULL    |                |
| url         | varchar(200) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

The url field would sometimes contain dupes but rather than remove all duplicates in the table, I need only where the field linksetid is equal to X.

The SQL below removes all duplicates in the table (which is not what I want)... but what I want is only the duplicates within a set range in the field linksetid. I know I'm doing something wrong, just not sure what is it.

DELETE FROM myfetcher USING myfetcher, myfetcher as vtable 
WHERE (myfetcher.fid>vtable.fid)
  AND (myfetcher.url=vtable.url)
  AND (myfetcher.linksetid='$linkuniq')

Just to be clear, if you have 2 rows with equal linksetid, you want both of them deleted (not just one of them) right? — HellishHeat, Aug 23 '12 at 14:10

score 2 · Accepted Answer · edited May 23 '17 at 12:20

Delete only records with linksetid=X. First EXISTS check case when all records are with linksetid=X then only one with min(fid) remains. The second EXISTS check case when there is a record with linksetid<>X then all records with linksetid=X will be removed:

NOTE: this query works in Oracle or MSSQL. For MYSql use next workaround:

DELETE FROM myfetcher 
where (myfetcher.linksetid='$linkuniq')
      and 
      (
      exists
      (select t.fid from myfetcher t where 
                 t.fid<myfetcher.fid 
                 and 
                 t.url=myfetcher.url
                 and 
                 t.linksetid='$linkuniq')

      or 

      exists
      (select t.fid from myfetcher t where 
                 t.url=myfetcher.url
                 and 
                 t.linksetid<>'$linkuniq')
       )

In MYSql you can't use update/delete command with subquery for the target table. So for MySql you can use following script. SqlFiddle demo:

create table to_delete_tmp as 
select fid from myfetcher as tmain 
     where (tmain.linksetid='$linkuniq')
      and 
      (
      exists
      (select t.fid from myfetcher t where 
                 t.fid<tmain.fid
                 and 
                 t.url=tmain.url
                 and 
                 t.linksetid='$linkuniq')

      or 

      exists
      (select t.fid from myfetcher t where 
                 t.url=tmain.url
                 and 
                 t.linksetid<>'$linkuniq')
       ) ;

delete from myfetcher where myfetcher.fid in (select fid from to_delete_tmp);

drop table to_delete_tmp;

Sql tells me 'You can't specify target table 'myfetcher' for update in FROM clause' — Chris Smith, Aug 23 '12 at 14:42
Please look at the fixed answer. I added a script to use in MySQL to avoid this error. — valex, Aug 23 '12 at 19:07
Thanks for your help but your script is not exactly doing what I want. It should remove duplicates within a certain range and leave one remaining. Currently it simply deletes everything at the given [id] that already exist in the table. — Chris Smith, Aug 27 '12 at 03:42

removing duplicate row from mysql where value equals something

1 Answers1