MySQL两表之间根据一个表的字段更新另一个表的字段无效

Question

问题描述

我想把a表的chapterid字段改为b表的id字段, 条件是courseid相同 可是下面的sql语句无效, 求大神解答..

相关代码

UPDATE cmf_course_lesson a,
 cmf_course_chapter b
SET a.chapterid = b.id
WHERE
    a.courseid = b.courseid; 

a表的表结构

b表的表结构

Answer 1

where条件加上主键就好了 where ... and A.id > 0

MySql运行在safe-updates模式下,导致非主键条件下无法执行update或者delete命令

Answer 2

UPDATE cmf_course_lesson a,
cmf_course_chapter b
JOIN cmf_course_lesson ON a.courseid = b.courseid
SET a.chapterid = b.courseid

Answer 3

UPDATE cmf_course_lesson a left join cmf_course_chapter b
ON a.courseid = b.courseid 
SET a.chapterid = b.courseid
where b.courseid > 0;