mysql出现You can’t specify target table for update in FROM clause

 

在mysql执行下面语句时报错:
 1 You can’t specify target table for update in FROM clause 
 2  
 3 UPDATE edu_grade_hgm_1
 4 SET exam_natures_new = '2'
 5 WHERE
 6 (outid, course_no) IN (
 7 SELECT
 8 a.outid,
 9 a.course_no
10 FROM
11 edu_grade_hgm_1 a
12 INNER JOIN edu_grade b ON a.outid = b.outid
13 AND a.course_no = b.course_no
14 )
15 AND exam_natures_new IS NULL;

 

  括号里的子查询和外面的upadate语句均没错,但加在一起便报错了。 那是因为那串英文错误提示就是说,不能先select出同一表中的某些值, 再update这个表(在同一语句中)。         所以先在子查询外面再套一层,修改sql如下:
 1 UPDATE edu_grade_hgm_1
 2 SET exam_natures_new = '2'
 3 WHERE
 4 (outid, course_no) IN (
 5 SELECT
 6 outid,
 7 course_no
 8 FROM
 9 (
10 SELECT
11 a.outid,
12 a.course_no
13 FROM
14 edu_grade_hgm_1 a
15 INNER JOIN edu_grade b ON a.outid = b.outid
16 AND a.course_no = b.course_no
17 ) AS temp
18 )
19 AND exam_natures_new IS NULL;

 

上一篇:解决MySQL Update:You can't specify target table for update in FROM clause


下一篇:mysql报错:You can't specify target table 't_frame' for update in FROM clause