Refresh all but one result?

Question

I am trying to update a table to remove everything except the first instance of the group. Basically, I have a table with data on vehicles related to the insurance policy. Each policy should have only power_unit. Everything else should be a towed unit. Unfortunately, the error duplicated the power supplies, and now I need to clean them. ~ 10 thousand records were recorded in the database, and ~ 4 thousand of which doubled the power of the blocks.

The important bits of my table (let's call it test1 now) are:

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(10) | NO   | PRI | NULL    | auto_increment | 
| policy_id  | int(10) | NO   |     | NULL    |                | 
| power_unit | int(1)  | NO   |     | 0       |                | 
+------------+---------+------+-----+---------+----------------+

And some sample data:

+----+-----------+------------+
| id | policy_id | power_unit |
+----+-----------+------------+
|  1 |         1 |          1 | 
|  2 |         1 |          1 | 
|  3 |         1 |          1 | 
|  4 |         2 |          1 | 
|  5 |         2 |          1 | 
|  6 |         2 |          1 | 
|  7 |         4 |          1 | 
|  8 |         4 |          1 | 
|  9 |         4 |          1 | 
| 10 |         5 |          1 | 
| 11 |         5 |          1 | 
| 12 |         6 |          1 | 
+----+-----------+------------+

Basically, I would like policy_id1 to have only one power_unit=1. The same goes for policy_id2, 3, 4, etc. For policy_id6, nothing should change (there is only one entry, and it is already power_unit).

, , , , , , .

Update:
, DELETE , UPDATE power_unit=0.

Answer 1

UPDATE  test1 t1,
        (
        SELECT  policy, MIN(ti.id) AS mid
        FROM    test1 ti
        GROUP BY
                policy
        ) tm
SET     power_unit = 0
WHERE   t1.policy = tm.policy
        AND t1.id <> tm.mid;