通过连接多个表来更新表

SELECT e.topicShortName, d.catalogFileID, e.topicID
FROM catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
LEFT JOIN catalog_lu_topics e ON a.topicID = e.topicID
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND e.parentID = 'top305'
AND a.sortorder =1
AND e.topicID = 'top318'

它为我获取了一行数据:

topicShortName  catalogFileID   topicID
 Welcoming       cfil960         top318

catalogFileID 转到“cfil123”。我有 topicID 对我来说,这是“318强”

编目文件ID 属于 catalog_files

我似乎无法理解实现这一目标的更新声明。。

我不介意做多次更新。但是在更新语句之后,上面的select查询应该返回cfil123。但我不能只更新所有使用catalogFileID的表。。

正确答案:

UPDATE catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
LEFT JOIN catalog_lu_topics e ON a.topicID = e.topicID
SET d.catalogFileID = 'Cfil123',
    b.catalogFileID = 'Cfil123',
    c.foreignKey = 'Cfil123'
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND e.parentID = 'top305'
AND a.sortorder =1
AND e.topicID = 'top318'