删除MySQL表中的重复项 - 当city_id相同时设置group_id

Question

I have table units in my database. In schema I have fields id, unit_id, group_id, city_id.

For simple I have 3 units:

(1, 1, 1, 1)
(2, 1, 2, 1)
(3, 1, 3, 2)

How can I remove useless groups id, when city id is the same. I have next result:

(1, 1, 1, 1)
(2, 1, 1, 1)
(3, 1, 3, 2)

I know how do this in PHP, but I think 'maybe MySQL has inbuild functions which i don't know' ;)

Regards

Answer 1

Okay, my solution:

UPDATE `ingame_units` INNER JOIN `ingame_groups` g1 ON `ingame_units`.`group_id`=g1.`id` LEFT JOIN `ingame_groups` g2 ON `ingame_units`.`group_id`<>g2.`id` AND g1.`city_id`=g2.`city_id` AND g1.`id`>g2.`id` AND g1.`game_id`=g2.`game_id` SET `ingame_units`.`group_id`=IFNULL(g2.`id`,g1.`id`)

Thanks one man to minus my post and don't try to help me. Regards :)