Sequential row update, skipping rows that have a value

I need to update a table with sequential numbering, but skipping numbers that are already in use.

The table has 3 (important) columns: id, name and order, where order is the display order of the buttons. When empty, it looks like this:

id	some_name	order_number
1	null	1
2	null	2
3	null	3
	...
10	null	10

User can fill in names (or leave them blank), but also mess around with the order of the buttons, even the empty ones. After saving the data, I end up with something like this:

id	some_name	order_number
1	null	6
2	red	5
3	null	8
4	null	9
5	blue	3
6	null	4
7	null	1
8	yellow	7
9	null	10
10	null	2

CREATE TABLE my_table(id tinyint(1),order_number tinyint(1),some_name varchar(40));
INSERT INTO my_table (id, order_number, some_name) VALUES
(1,6,NULL),(2,5,"red"),(3,8,NULL),(4,9,NULL),(5,3,"blue"),(6,4,NULL),(7,1,NULL),(8,7,"yellow"),(9,10,NULL),(10,2,NULL);

What I like to do is renumber the order_numbers in such a way that

if name is not null, keep the order_number chosen by the user
if name is null, renumber to the lowest possible incremental number
renumbering the null values should happen from the lowest to the highest id

In the above example buttons 2, 5 and 8 have a name, so I keep their order numbers (5, 3, 7). The other ones should be renumbered from 1 to 10 (ordered by id), skipping the order_numbers in use. In the end the table should look like this:

id	some_name	order_number	(why)	(Next seq number)
				1
1	null	1	name is empty, use next: 1	+1 = 2
2	red	5	full, keep number 5	2
3	null	2	empty, use next: 2	+1 = 3 : used in id=5 -> +1 = 4
4	null	4	empty, use next: 4	+1 = 5 : used in id=2 -> +1 = 6
5	blue	3	full, keep number 3	6
6	null	6	empty, use next: 6	+1 = 7 : used in id=8 -> +1 = 8
7	null	8	empty, use next: 8	+1 = 9
8	yellow	7	full, keep number 7	9
9	null	9	empty, use next: 9	+1 = 10
10	null	10	empty, use next: 10	+1 = 11

Selecting the id's where name = null is not a problem:

SELECT id FROM my_table WHERE some_name IS NULL

Result: 1, 3, 4, 6, 7, 9, 10 (id's 3, 5, 8 keep their order_number)

Also, selecting the sequential order_numbers not in use:

SELECT NR.new_number
FROM
(
  select @ordnum := @ordnum + 1 as new_number
  FROM my_table,(select @ordnum := 0) r
  ORDER BY new_number LIMIT 10
) NR
LEFT JOIN 
(
  SELECT order_number FROM my_table 
  WHERE some_name IS NULL
) RO
ON(RO.order_number = NR.new_number)
WHERE RO.order_number IS NOT NULL
ORDER BY NR.new_number

Result: 1, 2, 4, 6, 8, 9, 10 (numbers 3, 5, 7 are used)

The problem is how to connect these two queries (they have nothing in common except giving a result set of the same length), so that it picks the first id and the first order_number, updates, then picks the second row from each query, updates, and so on. Every kind of JOIN I tried so far either gives me the id's but the sequential numbering is stuck on 1, or it runs every id against every order_number, resulting in every id having order_number = 10.

Solution

Solved.

The two queries in my question needed something to connect them in a JOIN. Wrap them both up in a new SELECT, adding a counter to both. Now they can connect on the counter, which is the same for both queries (as both filter on the same thing: rows where name is null).

SELECT 
    /* result */
    empty_names.id,
    new_order.new_number
SELECT 
    @s1:=@s1+1 AS sq,
    id 
FROM(
    /* QUERY 1 goes here */
) AS empty_names
INNER JOIN
(
    SELECT 
        @s2:=@s2+1 AS sq,
        Y.new_number
    FROM(
        /* QUERY 2 goes here */
    ) Y,
    (select @s2:=0) s2
) new_order
/* connect on the counter */
ON empty_names.sq = new_order.sq

query 1		query 2		result
seq 1	id	seq 2	new number	id	new number
1	1	1	1	1	1
2	3	2	2	3	2
3	4	3	4	4	4
4	6	4	6	6	6
5	7	5	8	7	8
6	9	6	9	9	9
7	10	7	10	10	10

The result now can be used to update the table.