mysql You can't specify target table 'xxx' for update in FROM clause

含义：您不能在子句中为更新指定目标表'xxx'。

错误描述：删除语句中直接含select，如下：

DELETE FROM meriadianannotation WHERE

    SeriesID IN (

        SELECT

            SeriesID as tid

        FROM

            meriadianannotation

        GROUP BY

            SeriesID

        HAVING

            count(SeriesID) > 1

)

AND data NOT IN (

    SELECT

        min(data) as bid

    FROM

        meriadianannotation

    GROUP BY

        SeriesID

    HAVING

        count(SeriesID) > 1

)

解决方法：加临时表，如下，

DELETE FROM meriadianannotation WHERE

    SeriesID IN (

select t.tid from(

        SELECT

            SeriesID as tid

        FROM

            meriadianannotation

        GROUP BY

            SeriesID

        HAVING

            count(SeriesID) > 1

    ) t

)

AND data NOT IN (

select b.bid from(

    SELECT

        min(data) as bid

    FROM

        meriadianannotation

    GROUP BY

        SeriesID

    HAVING

        count(SeriesID) > 1

) b

)

巴特西

mysql You can't specify target table 'xxx' for update in FROM clause

最新文章

热门文章