报错详情:
### Cause: java.sql.SQLException: You can't specify target table 'setting_redundancy' for update in FROM clause
; uncategorized SQLException; SQL state [HY000]; error code [1093]; You can't specify target table 'setting_redundancy' for update in FROM clause; nested exception is java.sql.SQLException: You can't specify target table 'setting_redundancy' for update in FROM clause
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.2.15.RELEASE.jar:5.2.15.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.15.RELEASE.jar:5.2.15.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.15.RELEASE.jar:5.2.15.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) ~[mybatis-spring-2.0.2.jar:2.0.2]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.2.jar:2.0.2]
at com.sun.proxy.$Proxy131.update(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287) ~[mybatis-spring-2.0.2.jar:2.0.2]
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:63) ~[mybatis-plus-core-3.2.0.jar:3.2.0]
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61) ~[mybatis-plus-core-3.2.0.jar:3.2.0]
at com.sun.proxy.$Proxy165.updateDefalutValue(Unknown Source) ~[na:na]
at org.tobacco.cigaretteqrcodebase.infrastructure.cigaretteqrcodebase.repository.RedundancyRepositoryImpl.save(RedundancyRepositoryImpl.java:85) ~[classes/:na]
at org.tobacco.cigaretteqrcodebase.app.cigaretteqrcodebase.service.RedundancyServiceImpl.update(RedundancyServiceImpl.java:43) ~[classes/:na]
报错的大概意思是:您不能在 FROM 子句中指定目标表 ‘setting_redundancy’ 进行更新,报这个错误原因是mysql中不能查自己同时更新自己,需要把子查询的查询结果用个临时表装。
报错sql语句:update setting_redundancy set default_flag = 1 where id = (select id from setting_redundancy where enterprise_id = ? and value <= ? order by value desc limit 1)
修改后sql语句:update setting_redundancy set default_flag = 1 where id = (select a.id from (select id from setting_redundancy whereenterprise_id = ? and value <= ? order by value desc limit 1) a)