Auto increment null fields, leave non null fields as is

Question

I have a table items which has a field priority. Half of the elements in field priority have a unique value ranging from 1 to x. I want the elements where field priority is null to have incremental values of x+1 to y.

I have tried

UPDATE items
SET priority = IFNULL(priority, 0)

but that is not what I want. I want non null fields to stay as is, which I achieved with following:

SET priority = IFNULL(priority, 0)

and null fields to start being incremented from value, which I get with following:

SELECT MAX(priority)
FROM items;

So the only thing that comes to mind is something close to

UPDATE items
SET priority =
    IFNULL(priority, 0) + (SELECT MAX(priority)
                           FROM items)
WHERE priority IS NULL

However I am not too sure how I would go about doing so. Any hints or tips?

If you are not sure if it will work then just try and see if it worked — Alma Do, May 29 '15 at 13:41
How many do you have? Will you ever have to correct this again, or is it a one-off renumbering? The column does not already have an AUTO_INCREMENT I presume, because it would be null if it didn't. I can see a situation where you [add a new auto_increment column](http://stackoverflow.com/questions/9070764/insert-auto-increment-primary-key-to-existing-mysql-database/9070808#9070808) after setting its value to x+1 then updating the old existing values to the new column. Maybe. — Michael Berkowski, May 29 '15 at 13:42
@MichaelBerkowski this is a one off thing. And there are 30 items that have priority set from 1 to 30, and 40 items that have null priority. Column doesn't have auto increment or anything of the kind. — Quillion, May 29 '15 at 13:53

score 3 · Accepted Answer · edited May 29 '15 at 14:03

3

You can get the current maximum priority and store it in a variable. Then, bring that value into the query, say by using a cross join. You can increment the value in the set statement:

update items i cross join
       (select @x := max(priority) from items) maxp
    set priority = (@x := @x + 1)
    where priority is null;

edited May 29 '15 at 14:03

Quillion

6,346
11
60
97

answered May 29 '15 at 13:47

Gordon Linoff

1,242,037
58
646
786

1

@CarlSaldanha I did, and it will be visible once it is peer reviewed. I edited the actual answer and added `from items` in the join part. – Quillion May 29 '15 at 14:02

Auto increment null fields, leave non null fields as is

1 Answers1