不同于oracle和sqlserver,mysql并不支持在更新某个表的数据时又查询了它,而查询的数据又做了更新的条件,因此我们需要使用如下的语句绕过:
UPDATE teaching_department SET code_year = 2017, notice_code = (SELECT a.code + 1 FROM (SELECT MAX(notice_code) code FROM teaching_department WHERE department_id = 6284 and code_year = 2017) a) WHERE id = 106;
本地测试是通过的,但是在上到测试环境的时候,发现还是会报如下错误:
[SQL] UPDATE teaching_department SET code_year = 2017, notice_code = (SELECT a.code + 1 FROM (SELECT MAX(notice_code) code FROM teaching_department WHERE department_id = 6284 and code_year = 2017) a) WHERE id = 106; [Err] 1093 - You can't specify target table 'teaching_department' for update in FROM clause
对比版本发现,本地是5.6.25,测试环境是5.7.10,版本高的反而不支持了。只能改用另一种方式实现:
UPDATE teaching_department, (SELECT MAX(notice_code) code FROM teaching_department WHERE department_id = 6284 AND code_year = 2017) AS t SET code_year = 2017, notice_code = t.code + 1 WHERE id = 7
这种写法在各版本都是支持的。语法见UPDATE Syntax
后来发现,这是mysql 5.7.6版本出现的一个bug,并且在5.7.11版本中修复了。参考