Thursday, 8 August 2013

Mysql query using IN with group_concat result

Mysql query using IN with group_concat result

I'm trying to clean a db with duplicate records. I need to move the
reference to a single record and delete the other one.
I have two tables: Promoters and Venues, each has a reference to a table
called cities. The problem is that there are cities with the same name and
different ids, that have a relation with venues and promoters.
With this query I can group all promoters and venues with a single city
record:
SELECT c.id as id, c.name as name, GROUP_CONCAT( DISTINCT p.id ) as
promoters_ids, GROUP_CONCAT( DISTINCT v.id ) as venues_ids
FROM cities as c
LEFT JOIN promoters as p ON p.city_id = c.id
LEFT JOIN venues as v ON v.city_id = c.id
WHERE c.name IN ( SELECT name from cities group by name having
count(cities.name) > 1 )
GROUP BY c.name
Now I want to run an UPDATE query on promoters, setting the city_id equals
to the result of the query above.
Something like this:
UPDATE promoters AS pr SET pr.city_id = (
SELECT ID
FROM (
SELECT c.id as id, c.name as name, GROUP_CONCAT( DISTINCT p.id
) as promoters_ids
FROM cities as c
LEFT JOIN promoters as p ON p.city_id = c.id
WHERE c.name IN ( SELECT name from cities group by name having
count(cities.name) > 1 )
GROUP BY c.name
WHERE pr.id IN promoters_ids
) AS T1
)
How can I do this?
Thanks

No comments:

Post a Comment