在 sql 查詢中使用 WITH

我正在嘗試#用數字替換，但order by被忽略了，我被告知使用WITH或添加限制來移動子查詢，我嘗試了後者但它沒有用，order by我不知道如何使用WITH以下查詢。

update items
join (select id, @num := @num + 1 as num
   from items, (select @num := 0) items
    where parent_id =  159133    order by name) items2
on items2.id = items.id
set items.description = replace(items.description, '#' COLLATE utf8_unicode_ci, cast(num as char(10))) 
WHERE parent_id = 159133 and type='photo';

您的查詢可以在沒有子查詢/連接的情況下編寫：

UPDATE items
  SET description = replace(description, '#', cast(@num:=@num+1 as char(10))) 
WHERE parent_id = 159133 
  AND type='photo'
  AND @num := 1       -- @num initialization by Akina
ORDER BY name ASC
;

引用自：https://dba.stackexchange.com/questions/228888