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. 网上说再加一层查询可以解决:
UPDATE t_user_asset SET f_cashAmount =
(
SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290
)
WHERE f_userId = 290
修改成在select外边套一层,让数据库认为你不是查的同一表的数据
UPDATE t_user_asset SET f_cashAmount =
(
SELECT ub.cashAmount FROM
(
SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290
) ub
)
WHERE f_userId = 290
这个方法参考:http://blog.csdn.net/yhl_jxy/article/details/50856619