mysql更新表資料時報錯 You can't specify target table 'RES_CATALOG_CLASSIFY' for update in FROM clause

You can't specify target table for update in FROM clause含義:不能在同一表中查詢的資料作為同一表的更新資料。

将sql語句

UPDATE RES_CATALOG_CLASSIFY
SET CATALOG_SORT = CATALOG_SORT + 1
WHERE
    ID = (
        SELECT
            ID
        FROM
            `res_catalog_classify`
        WHERE
            PARENT_ID = '001'
        AND CATALOG_SORT = 7
    )      

改為

UPDATE RES_CATALOG_CLASSIFY
SET CATALOG_SORT = CATALOG_SORT + 1
WHERE
    ID = (
        SELECT a.ID FROM (SELECT
            ID
        FROM
            `res_catalog_classify`
        WHERE
            PARENT_ID = '001'
        AND CATALOG_SORT = 7) a
    )      

就可以了