Question 200112 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete duplicates from joined table MySQL

please help me delete from the ‘Component’ table all records that have the same ‘country’ and they belong to the same ‘Page’, leave only one record with the country that has the newest createdAt?

Relationship between tables:
Page -> PageComponent.pageId -> PageComponent.componentId -> Component.id

Sandbox with table and data https://www.db-fiddle.com/f/CnFouhFZyfkEpzGKij5eq/2

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a solution:

    WITH duplicateIdTable AS (
SELECT
       any_value(c.country),
       LAG(c.id) over (partition by p.id, c.country order by c.createdAt) as duplicateComponentId,
       LAG(pc.id) over (partition by p.id, c.country order by c.createdAt) as duplicatePageComponentId,
       p.id
FROM Page p
         JOIN PageComponent pc ON p.id = pc.pageId
         JOIN Component c ON pc.componentId = c.id
)
DELETE Component, PageComponent FROM Component
                                INNER JOIN PageComponent ON PageComponent.componentId = Component.id
                                INNER JOIN duplicateIdTable ON duplicateIdTable.duplicateComponentId = Component.id
                                and duplicateIdTable.duplicatePageComponentId = PageComponent.id
   WHERE Component.id = duplicateIdTable.duplicateComponentId
and PageComponent.id = duplicateIdTable.duplicatePageComponentId

  2. 0

    I think I am not getting it all but please try this:

    DELETE 
main 
FROM 
`Component` main 
WHERE main.id NOT IN (
SELECT MAX(c.`id`) 
FROM `Page` p 
JOIN `PageComponent` pc ON pc.`pageId`=p.`id`
JOIN (SELECT * FROM `Component`) c ON c.`id`=pc.`componentId`
GROUP BY c.`country`, p.`id` HAVING COUNT(*)>1
)

    Let me know if I have messed up something.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search