CODEWITHSUNDEEP
mysqlsql
David
David

Reputation: 7303

SQL update and increment column using a subquery from the same table

Here's my fiddle. My query:

update foos
set foo_id = ((SELECT max_foo_id FROM (SELECT MAX(foo_id) AS max_foo_id FROM foos) AS temp_foos) + 1)
where foo_id is null;

produces values 1, 2, 2, 2 for foo_ids (subquery only run once?) but I'd like it to give me 1, 2, 3, 4

Upvotes: 1

Views: 1213

Answers (1)

John Woo
John Woo

Reputation: 263723

how about this?

UPDATE  foos a
        INNER JOIN
        (
            SELECT  a.bar_ID,
                    @rn := @rn + 1 row_Num
            FROM    foos a,(SELECT @rn := (SELECT MAX(foo_ID) FROM foos)) b
            WHERE   a.foo_ID IS NULL
        ) b ON a.bar_ID = b.bar_ID
SET     a.foo_id = b.row_Num

UPDATE 1

Upvotes: 2

Related Questions