需求:直接使用MySQL语句 更新 数据库中 字段 的值
UPDATE `config`
SET `value` = REPLACE (`value`, '"pn",', '')
WHERE
id IN (
SELECT id FROM `sys_config` WHERE `value` LIKE '%pn%dwgno%' AND `key` NOT LIKE '%List%'
);
该sql语句执行报错:
1093 - Table 'config' is specified twice, both as a target for 'UPDATE' and as a separate source for data
即
MySQL中不允许先select出同一表中的某些值,再update这个表(在同一语句中)
做如下修改
修改1: 使用inner join
update `config` as c
INNER JOIN (
select id as tempID from `config` where `value` like '%pn%dwgno%' and `key` not like '%List%'
) tempc on tempc.tempID=c.id
set c.`value` = REPLACE(c.`value`, '"pn",', '')
修改2:多加个嵌套
UPDATE `config`
SET `value` = REPLACE (`value`, '"pn",', '')
WHERE
id IN (
SELECT id FROM
(
SELECT id FROM `config` WHERE `value` LIKE '%pn%dwgno%' AND `key` NOT LIKE '%List%'
) tempC
)
经测, 执行成功