mysql query update duplicates

Question

I have a column called version in that there are numbers from 2-300.000.

In that column I have deleted some Version numbers, so the numbers are not continuous from 2-300.000, there are missing some numbers.

I need to correct it so it will be continuous again. This is easy and could be done with

SET @ver := 1;
UPDATE geo_patch SET Version = ( SELECT @ver := @ver + 1 ) ORDER BY Version DESC;

My problem is there are duplicates with the same Version number, and I need to keep them as duplicates but still update them with a new version number.

You need a rank. Have you checked this? http://stackoverflow.com/questions/3333665/mysql-rank-function (the answer with 15 votes) — Multisync, Oct 19 '14 at 14:07

score 0 · Accepted Answer · answered Oct 19 '14 at 14:20

You want to update existing version numbers with a new, sequential number, but keep the duplicates in tact. For this, use a join and calculate the new version in a subquery:

UPDATE geo_patch gp JOIN
       (select distinct version, (@ver := @ver + 1) as newver
        from geo_patch cross join (select @ver := 0) vars
        order by version
       ) gp2
       ON gp.version = gp2.version
    set gp.Version = gp2.newver;

MySQL can be finicky about variables with group by. I'm not sure if the same weirdness exists with distinct. So the above could put things in the wrong order. The following should definitely work:

UPDATE geo_patch gp JOIN
       (select version, (@ver := @ver + 1) as newver
        from (select distinct version from geo_patch) gp2 cross join
             (select @ver := 0) vars
        order by version
       ) gp2
       ON gp.version = gp2.version
    set gp.Version = gp2.newver;

mysql query update duplicates

1 Answers1