You can't specify target table 'TS_AUTH_ADMIN' for update in FROM clause记录
来源:互联网 发布:被淘宝卖家恐吓 编辑:程序博客网 时间:2024/06/03 19:45
1. 报错:You can't specify target table 'TS_AUTH_ADMIN' for update in FROM clause,
百度查到说是,不能在同一语句中先select出同一表中的某些值,再update这个表 。
我原本的sql是:(删除角色的时候,把管理员拥有的该角色信息也删除)
update TS_AUTH_ADMIN set ROLE_IDS =CASE WHEN ROLE_IDS in (select ROLE_IDS FROM TS_AUTH_ADMIN WHERE instr(ROLE_IDS,#{roleId}+',')>0) THEN replace(ROLE_IDS,#{id}+',','') WHEN ROLE_IDS in (select ROLE_IDS FROM TS_AUTH_ADMIN WHERE instr(ROLE_IDS,#{roleId})>0) THEN replace(ROLE_IDS,#{id},'') ELSE ROLE_IDS END和:update TS_AUTH_ADMIN set ROLE_NAME =CASE WHEN ROLE_NAME in (select ROLE_NAME FROM TS_AUTH_ADMIN WHERE instr(ROLE_NAME,#{roleName}+',')>0) THEN replace(ROLE_NAME,#{roleName}+',','') WHEN ROLE_NAME in (select ROLE_NAME FROM TS_AUTH_ADMIN WHERE instr(ROLE_NAME,#{roleName})>0) THEN replace(ROLE_NAME,#{roleName},'') ELSE ROLE_NAME END
2. 后来我改成类似如下写法:
update TS_AUTH_ADMIN set ROLE_NAME =(CASE WHEN instr(ROLE_NAME,'test2,')>0 THEN replace(ROLE_NAME,'test2,','') WHEN instr(ROLE_NAME,'test2')>0 THEN replace(ROLE_NAME,'test2','') ELSE ROLE_NAME END), ROLE_IDS =(CASE WHEN instr(ROLE_IDS,'6,')>0 THEN replace(ROLE_IDS,'6,','') WHEN instr(ROLE_IDS,'6')>0 THEN replace(ROLE_IDS,'6','') ELSE ROLE_IDS END)
测试通过:(当然我这样改,没有重现问题,不算解决,只是对我的业务是可用的)
3. 网上说再加一层查询可以解决:
修改成在select外边套一层,让数据库认为你不是查的同一表的数据 这个方法参考:http://blog.csdn.net/yhl_jxy/article/details/50856619
1 0
- You can't specify target table 'TS_AUTH_ADMIN' for update in FROM clause记录
- mysql update语句问题:You can't specify target table 'user' for update in FROM clause
- You can't specify target table 'table name' for update in FROM clause
- MySql报错: You can't specify target table 'table name' for update in FROM clause
- 【MySQL】1093 You can't specify target table 'table name' for update in FROM clause
- [Err] 1093 - You can't specify target table 'table' for update in FROM clause处理
- You can't specify target table 't' for update in FROM clause
- 【mysql 】sql删除重复记录 You can't specify target table '表名' for update in FROM clause
- Mysql - You can't specify target table 'dataproduct' for update in FROM clause
- You can't specify target table 'wms_cabinet_form' for update in FROM clause
- MySQL报错“ you can't specify the target table for ‘update’ in from clause”
- You can't specify target table 'sc1' for update in FROM clause
- [Err] 1093 - You can't specify target table 's' for update in FROM clause
- mysql中You can't specify target table for update in FROM clause错误
- You can't specify target table 'sdb_goods' for update in FROM clause
- You can't specify target table 'A' for update in FROM clause
- MYSQL 临时表 #1093 - You can't specify target table 'dealer_admin' for update in FROM clause
- MySQL "You can't specify target table 'X' for update in FROM clause" 错误解决方法
- 使用ListView更新数据一直闪烁的解决办法
- Win下用Pycharm远程连接虚拟机Linux的环境准备
- 关于强制换行的总结
- 关于domain adaptation
- Javascript 闭包与高阶函数 ( 一 )
- You can't specify target table 'TS_AUTH_ADMIN' for update in FROM clause记录
- PyQt中的各种提示框
- 光学相干断层成像术(OCT)
- Android 单独控件实现不同字体大小、不同颜色
- 获取resource中的参数值
- mysql的常用基本操作
- volatile实现原理——基于硬件的角度
- 面试常见的算法题
- CS0219号警告是什么