java.sql.SQLException: You can‘t specify target table ‘XXX‘ for update in FROM clause解决方案

我们来看报错日志：

### Error updating database.  Cause: java.sql.SQLException: You can't specify target table 'file' for update in FROM clause
### SQL: DELETE FROM file     WHERE (url = ? AND Id NOT IN(SELECT MAX(Id) FROM file))
### Cause: java.sql.SQLException: You can't specify target table 'file' for update in FROM clause] with root cause

我们可以出控制台看到这个Erro信息：

java.sql.SQLException: You can't specify target table 'file' for update in FROM clause

 大致翻译是：不能在 FROM 子句中指定要更新的目标表，简单一点来说就是不能在DQL的时候又在DML

可以看到笔者上面的SQl是这样写的

DELETE FROM file WHERE (url = ? AND Id NOT IN(SELECT MAX(Id) FROM file))

笔者的Mybatis-Plus的代码 

解决方案：将DQL的结果用临时表存起来 ，SQl语句如下

DELETE FROM file WHERE url = ? AND 
Id NOT IN
(SELECT temp.Id FROM(SELECT MAX(Id) AS Id FROM file) temp)