CODEWITHSUNDEEP
mysql
Ben Fortune
Ben Fortune

Reputation: 32127

UPDATE with SELECT statement but re-select on each update

I'm trying to update multiple rows in MySQL by selecting the highest number and adding 1 to it to generate the next one.

UPDATE orders SET delivery_number = (
    SELECT new_number FROM (
        SELECT (
            MAX(delivery_number) + 1
        ) AS new_number FROM order_invoice
    ) AS result
) WHERE delivery_number = 0 AND invoice_number != 0;

The query above only seems to SELECT once, then use the same number for each update.

How can I force it to scan the table again for the highest number on each update?

Upvotes: 0

Views: 43

Answers (1)

splash58
splash58

Reputation: 26153

update orders, (select @n:=max(delivery_number) from order_invoice) n 
   set delivery_number = @n:=@n+1 
   where delivery_number = 0 and invoice_number != 0;

Upvotes: 1

Related Questions