写这篇博客,是因为我想批量修改我博客某个分类下文章的状态,在修改时发生了上面的错误,随记录网上一篇情况相似的文章,我的博客是用typecho搭建的,下面附上最终可以运行成功的代码:

UPDATE tc_contents tc SET tc.`status` = 'private' WHERE tc.cid in (
	SELECT tmp.cid FROM(
		SELECT c.cid FROM tc_contents c 
		LEFT JOIN tc_relationships s on s.cid = c.cid 
		WHERE s.mid = 136 and c.`status` = 'publish') tmp
)

下面是在网上摘录与此情况类似的文章,供参考。


有一个表示地区的表,表结构与数据大概如下表。

IDNAMEPARENT_ID
1中国1
2广东省2
3广州市3
4荔湾区3
5越秀区3
6番禺区3
7小谷围街道6

现为了查询方便,需要加一列PARENT_NAME,用以表示上级地区的名称(虽然不符合第三范式,传递依赖,但有时为了业务上的可行性、便利性,可以按实际情况考虑)

IDNAMEPARENT_IDPARENT_NAME
1中国1
2广东省2
3广州市3
4荔湾区3
5越秀区3
6番禺区3
7小谷围街道4

 

附,表的DDL、DML:

-- ----------------------------
-- Table structure for `t_area`
-- ----------------------------

CREATE TABLE `t_area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `parent_name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_area
-- ----------------------------
INSERT INTO `t_area` VALUES ('1', '中国', null, null);
INSERT INTO `t_area` VALUES ('2', '广东省', '1', null);
INSERT INTO `t_area` VALUES ('3', '广州市', '2', null);
INSERT INTO `t_area` VALUES ('4', '荔湾区', '3', null);
INSERT INTO `t_area` VALUES ('5', '越秀区', '3', null);
INSERT INTO `t_area` VALUES ('6', '番禺区', '3', null);
INSERT INTO `t_area` VALUES ('7', '小谷围街道', '6', null);


这时,需要根据已有信息,更新PARENT_NAME的数据,就有了如下的SQL:

/* [Err] 1093 - You can't specify target table 't' for update in FROM clause */
update t_area t set t.parent_name = (select t2.name from t_area t2 where t.parent_id = t2.id);

报出“1093 - You can't specify target table 't' for update in FROM clause”的异常。意思,意思大约为,你不能指定更新的目标表在FROM子句中(英文不好,即使认识各个单词,串起来就不行了。。。)


就如文档所述“Currently, you cannot update a table and select from the same table in a subquery.”,见http://dev.mysql.com/doc/refman/5.5/en/update.html。

不知道MySQL为什么不允许这样操作,猜,可能是担心更新的表与查询的表为同一表会存在嵌套递归?还是担心效率的问题呢?

如果,将该表在嵌套一层,即“(select * from t_area) st”这样得出一个临时的结果集,即无报错,但,这性能较差,仅仅适合较小的数据量的。(见此讨论帖:http://stackoverflow.com/questions/17742214/you-cant-specify-target-table-name-for-update-in-from-clause)。


修改后如下:

update t_area t set t.parent_name = (select t2.name from (select * from t_area) t2 
where t.parent_id = t2.id);

具体针对这个需求,更简单的方式,貌似也可以:

update t_area t, t_area t2 set t.parent_name = t2.name where t.parent_id = t2.id;