sql - Mysql query using IN with group_concat result -

i'm trying clean db duplicate records. need move reference single record , delete other one.

i have 2 tables: promoters , venues, each has reference table called cities. problem there cities same name , different ids, have relation venues , promoters.

with query can group promoters , venues single city record:

select c.id id, c.name name, group_concat( distinct p.id ) promoters_ids, group_concat( distinct v.id ) venues_ids cities c left join promoters p on p.city_id = c.id left join venues v on v.city_id = c.id c.name in ( select name cities group name having count(cities.name) > 1 ) group c.name 

now want run update query on promoters, setting city_id equals result of query above.

something this:

    update promoters pr set pr.city_id = (         select id         (             select c.id id, c.name name, group_concat( distinct p.id ) promoters_ids             cities c             left join promoters p on p.city_id = c.id              c.name in ( select name cities group name having count(cities.name) > 1 ) , pr.id in promoters_ids             group c.name             ) t1       ) 

how can this?

thanks

if understand correctly, want remove duplicate cities (in end), need update promoters linked of cities want remove in process.

i think makes sense use lowest id of of cities same name (could highest well, want specify @ least, , don't leave me.

so in order right id promoter, need to: select lowest id of cities have same name city linked promoter.

fortunately, demand fits snuggly query:

update promoters pr  set pr.city_id = (   select      -- select lowest id ..     min(c.id)       -- .. of cities ..     cities c     -- .. have same name ..     inner join cities pc on pc.name = c.name       .. city linked promoter being updated     pc.id = pr.city_id   group     c.name) 

the trick join cities on name, can cities same name. think tried same in clause, that's little more complex needs be.

i don't think need group_concat @ all, besides checking if inned query returns correct cities indeed, although doesn't make sense, since you're grouping on name. when written this, can tell there should no way can go wrong:

  select      -- select lowest id ..     min(c.id) id,     group_concat(c.name) names --< grouped this, why...       -- .. of cities ..     cities c     -- .. have same name.     inner join cities pc on pc.name = c.name   group     c.name 

i hope understood question correctly.