自更新表数据

当执行一个简单的update语句时，发生了1093报错，语句没问题，逻辑也没问题，也很确定自己的代码不会引发数据错误，但是MySQL不允许这样执行

1093 - You can't specify target table 'TABLE_QWE' for update in FROM clause

报错语句

样例1：

UPDATE TABLE_QWE
SET TCLOSE = RAND() * 10000 
WHERE
    N_ID IN( SELECT
    N_ID 
FROM
    `TABLE_QWE` QWE
    LEFT JOIN TABLE_ASD ASD ON ASD.id = QWE.id 
WHERE
    ASD.sid = 150);

1093 - You can't specify target table 'TABLE_QWE' for update in FROM clause

解决方案

SET SQL_SAFE_UPDATES = 0;

UPDATE TABLE_QWE 
SET TCLOSE = RAND() * 10000 
WHERE
    N_ID IN( SELECT N_ID FROM (SELECT
    N_ID 
FROM
    `TABLE_QWE` QWE
    LEFT JOIN TABLE_ASD ASD ON ASD.id = QWE.id 
WHERE
    ASD.sid = 150)A);

SET SQL_SAFE_UPDATES = 1;

或者

update TABLE_QWE join TABLE_ASD ON TABLE_QWE.id = TABLE_ASD.id SET TABLE_QWE.TCLOSE =  RAND() * 10000 WHERE  TABLE_ASD.sid = 150

删除操作

内部再嵌套一层即可，也有可能需要SET SQL_SAFE_UPDATES = 0;

DELETE FROM table WHERE col in (SELECT col FROM (SELECT b.col FROM table a
right join table b on a.col = b.col
where
a.col not null)T) ;