SystemicPlural SystemicPlural - 5 months ago 9
SQL Question

How can I update a mysql query with the row count

I want to reorder a display column so that the order is sequential with no gaps. Is something like the following possible

UPDATE foo
INNER JOIN bar ON foo.barid = bar.id
SET foo.display = !rowcount!
WHERE bar.baz = 1
ORDER BY foo.display;

Answer

Did you want this?

UPDATE foo
INNER JOIN (
    SELECT t2.id, @rowNo := @rowNo + 1 AS rowcount
    FROM foo t1
    INNER JOIN bar t2 ON t1.barid = t2.id
    INNER JOIN (SELECT @rowNo := 0) t
    WHERE t2.baz = 1
    ORDER BY t1.display) bar ON foo.barid = bar.id 
SET foo.display = bar.rowcount