sql - Find all NULL values and set them to lowest unused number using MySQL query -

i want find all null values in column parameter_id , set them lowest unused parameter_id.

i have query find lowest unused parameter_id, know how list of null values.

select min(t1.parameter_id)+1 table t1 not exists (select * table t2 t2.parameter_id = t1.parameter_id+1) 

i can list of rows parameter_id=null, make query find current lowest unused parameter_id , update parameter_id lowest unused number. since table has 50.000 rows, approach create thousands of queries (50.000 * 2 per row).

is there way run "single query" find parameter_id=null , update them current lowest unused parameter_id?

here table decrtiption (mysql 5.5):

id (int) primary key, auto_increment parameter_id (int) default null 

sample data:

# id, parameter_id 1, null 2, 1 3, null 4, 5 5, 3 

desired result:

# id, parameter_id 1, 2 2, 1 3, 4 4, 5 5, 3 

edit:

i distilled want single query. need run query until there 0 rows affected update.

update `table` set parameter_id=   (select *         (select min(t1.parameter_id)+1       `table` t1       not exists           (select *            `table` t2            t2.parameter_id = t1.parameter_id+1)) t4) parameter_id null limit 1 

the following enumerates unused parameter ids:

select t.*, (@rn := @rn + 1) seqnum table t cross join      (select @rn := 0) params not exists (select 1 table t2 t2.parameter_id = t.id) order t.id; 

(you might want put in temporary table index on seqnum subsequent query.)

the problem getting join key update. here bit of kludge: i'm going add column, enumerate it, , drop it:

alter table `table` add column null_seqnum;  update `table` t cross join (select @rn1 := 0) params     set null_seqnum = (@rn1 := @rn1 + 1)     parameter_id null;  update `table` t join        (select t.*, (@rn := @rn + 1) seqnum         `table` t cross join              (select @rn := 0) params         not exists (select 1 `table` t2 t2.parameter_id = t.id)         order t.id        ) tnull        on t.null_seqnum = tnull.seqnum     set t.parameter_id = tnull.id;  alter table `table` drop column null_seqnum;