我们来看报错日志:
### 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)