我创建了这样一个表
CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4;
我想查询word='aaae’时,将满足条件的记录的num字段修改为15,于是
UPDATE words SET num = 15 WHERE id IN (SELECT id FROM words WHERE word = 'aaae')
但是这样是报错的
> 1093 - You can't specify target table 'words' for update in FROM clause
在Mysql里,不允许先select一个表的记录,在按此条件进行更新和删除同一个表的记录。
解决办法一:
将select得到的结果,再通过中间表select一遍,这样就规避了错误,原先的语句可以修改为
UPDATE words set num = 15 where id in (select id from (select id from words where word = 'aaae') a)
解决方法二:
使用join,查询关联的同时对查询结果集进行更新
update words w join words ww on w.id = ww.id and w.word = 'aaae' set w.num = 15