Update all but one result?

mysql, sql-update

Solution

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;

Problem

I'm trying to update a table to remove all but the first instance of a group. Basically, I have a table with vehicle data related to an insurance policy. Each policy should only have one `power_unit`. Everything else should be a towed unit. Unfortunately, a bug has been duplicating power units, and now I need to clean this up. There are ~10k records in the database, and ~4k of them have doubled up power units. The important bits of my table (call it test1 for 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'd like to end up where `policy_id` 1 has only one `power_unit=1`. Same for `policy_id` 2, 3, 4, etc. For `policy_id` 6, nothing should change (there is only one entry, and it is a power_unit already). I don't know if this is possible, but it was an intriguing problem for me, so I thought you guys might find it the same. Update: I don't want to `DELETE` the rows, just `UPDATE` them to have `power_unit=0`.

Original source