I want to reorder a display column so that the order is sequential with no gaps. Is something like the following possible
INNER JOIN bar ON foo.barid = bar.id
SET foo.display = !rowcount!
WHERE bar.baz = 1
ORDER BY foo.display;
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