mysql的1093错误You can‘t specify target table ‘t‘ for update in FROM clause 简单解决

mysql>delete from twhere id in (select idfrom t where id < 5);  

ERROR1093 (HY000): You can't specify target table 't' for update in FROM clause

改为下面就OK

delete from t where id in(               select * from (select idfrom twhere id <5)    tmp                  );  

主句(      select * from (从句)   temp)     )

set @id=7748;
select * from tb_iot where id=@id;
delete from tb_iot where pid in (        select * from (select id from tb_iot where pid=@id and model_id=170)    tmp   ); #智能设备集合 先删子级
DELETE FROM tb_iot  where pid=@id or id=@id; #再删本级及自己

 

 

 

 

 

 

删除重复，但保留最小id项。

 DELETE
FROM
    file
WHERE
    fileaddress IN (
    select * from ((
        SELECT
            fileaddress 
        FROM
            file
        GROUP BY
            fileaddress
        HAVING
            count(fileaddress) > 1
            ) a)
    )
AND id NOT IN (
 select * from ((
    SELECT
        min(id)
    FROM
        file
    GROUP BY
        fileaddress
    HAVING
        count(fileaddress) > 1
         ) b)
)