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

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

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 :)

If I understand correctly, you want to delete rows where group_id and city_id are equal? If so, it's very simple:

DELETE FROM units WHERE group_id = city_id

if I understand your question correctly you want to all group_id have same value from the same city_id. Basically your first table in question is what you have and the second one is desired result. If that's the case your query could look like this:

UPDATE table1 
INNER JOIN (SELECT * FROM table1 GROUP BY city_id) AS tx
ON table1.city_id = tx.city_id
SET table1.group_id = tx.group_id;

Here is the SQL Fiddle to see how it's work.

If you want to completely remove values and to hold only distinct city_id then you can do that with query like this:

DELETE table1 FROM table1 
INNER JOIN (SELECT * FROM table1 GROUP BY city_id) AS tx
ON table1.city_id = tx.city_id
WHERE table1.group_id != tx.group_id;

Here is SQL Fiddle for that!

In this case your result table will be without row with id 2...

GL!