mysql - SQL 根据另一个不直接相关的表更新列

Question

我在 Moodle 工作，由于全年登记册在年底一次性标记，我们的徽章没有在正确的日期颁发。

类(class)日期是mdl_facetoface_sesion_dates.timestart徽章日期为 mdl_badgeissued.dateissued

我尝试过各种版本，下面似乎是我得到的最接近的版本。

我目前在 phpmyadmin 工作。

UPDATE
    mdl_badge_issued
SET
     mdl_badgeissued.dateissued = mdl_facetoface_sesion_dates.timestart
FROM mdl_badge_issued
INNER JOIN mdl_badge_issued.userid ON mdl_badge_issued.userid = mdl_user.id 
INNER JOIN mdl_badge ON mdl_badge_issued.badgeid = mdl_badge.id 
INNER JOIN mdl_course ON cmdl_course.id = mdl_badge.courseid 
INNER JOIN mdl_facetoface ON mdl_facetoface.course = mdl_course.id 
INNER JOIN mdl_facetoface_sessions ON mdl_facetoface_sessions.facetoface = mdl_facetoface.id 
INNER JOIN mdl_facetoface_sessions_dates ON mdl_facetoface_sessions_dates.sessionid = mdl_facetoface_sessions.id

WHERE mdl_badgeissued.badgeid='62'

我想复制mdl_badgeissued.dateissued = mdl_facetoface_sesion_dates.timestart

Answer 1

尝试这样:(你的语法错误)

UPDATE mdl_badge_issued
INNER JOIN mdl_badge_issued.userid ON mdl_badge_issued.userid = mdl_user.id 
INNER JOIN mdl_badge ON mdl_badge_issued.badgeid = mdl_badge.id 
INNER JOIN mdl_course ON cmdl_course.id = mdl_badge.courseid 
INNER JOIN mdl_facetoface ON mdl_facetoface.course = mdl_course.id 
INNER JOIN mdl_facetoface_sessions ON mdl_facetoface_sessions.facetoface = mdl_facetoface.id 
INNER JOIN mdl_facetoface_sessions_dates ON mdl_facetoface_sessions_dates.sessionid = mdl_facetoface_sessions.id
SET mdl_badgeissued.dateissued = mdl_facetoface_sesion_dates.timestart
WHERE mdl_badgeissued.badgeid='62'