Delete duplicates for multiple columns in JOIN on same table

Question

I am trying to make a delete from joined same table like this:

DELETE FROM `sp10_seo_url` AS sp1 JOIN
( 
        SELECT seo_url_pk, COUNT(*) AS maxc 
        FROM `sp10_seo_url` 
        GROUP BY seo_url_entity_type, seo_url_entity_id, seo_url_language_fk 
        HAVING maxc  > 1 
) AS sp2

ON sp1.seo_url_pk = sp2.seo_url_pk

However I am getting a mysql error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS sp1 JOIN ( SELECT seo_url_pk, COUNT(*) AS maxc FROM `sp10_s' at line 1

And I am not sure at all where the error is. The inner query runs just fine and returns the expected set of results. The "ON" keys are properly named (same since we are talking about the same table).

I guess the idea of the query is pretty clear (clean the table of different rows have the same set of values for the three "group by" columns. Is there another way to do this?

Thanks!

DELETE sp1... ?? But a better question might elicit a better answer. — Strawberry, Dec 08 '14 at 17:36

score 1 · Accepted Answer · edited May 23 '17 at 11:50

you can "cheat" mysql with a double indirection (as explained here Deleting a row based on the max value):

delete from `sp10_seo_url`
where seo_url_pk in (
  select seo_url_pk from (
  SELECT seo_url_pk
  FROM `sp10_seo_url` sp1,
  (
    SELECT seo_url_entity_type, seo_url_entity_id, seo_url_language_fk 
    FROM `sp10_seo_url` 
    GROUP BY seo_url_entity_type, seo_url_entity_id, seo_url_language_fk 
    HAVING count(*)  > 1 
  ) sp2
  where sp1.seo_url_entity_type    = sp2.seo_url_entity_type
  and   sp1.seo_url_entity_id      = sp2.seo_url_entity_id
  and   sp1.seo_url_language_fk    = sp2.seo_url_language_fk
  ) t
  );

http://sqlfiddle.com/#!2/899ff5/1

Delete duplicates for multiple columns in JOIN on same table

1 Answers1