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
– Strawberry, Commented Dec 8, 2014 at 17:36

Community · Accepted Answer · 2017-05-23 11:50:55Z

1

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

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Dec 8, 2014 at 19:54

syllabus

5813 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Delete duplicates for multiple columns in JOIN on same table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related