在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;