mysql - 具有SELECT值的UPDATE SQL列

我有一个名为loanofficer的列在一个名为prequals_created的表中，它列出了一个对应于那个贷款官员的数字。我想更新此列，使其具有贷款官员名称，而不是编号，该编号位于名为appusers的表中，其中fullname与loanofficerid匹配。为此，我首先使用select生成一个贷款官员名称和id表，作为一对，使用名为loan_pairing的“中间”表。但是，我的语法有些问题。这是我的代码：

UPDATE
    prequals_created AS pc
SET
    pc.loanofficer = lonames.fullname
FROM
    (SELECT DISTINCT a.fullname, lp.loanofficer
     FROM appusers AS a
     JOIN loan_pairing AS lp
        ON a.id = lp.loanofficer
     JOIN prequals_created AS pc
        ON lp.loanofficer = pc.loanofficer) AS lonames
WHERE
    pc.loanofficer = lonames.loanofficer

嵌套在from语句中的select语句是正确的，并返回贷款官员的姓名及其各自的id。
我的语法到底怎么了？

最佳答案

在MySQL中的多表更新语句中，连接在SET之前。我认为这应该对你有用：

UPDATE  prequals_created AS pc
        JOIN loan_pairing AS lp
             ON lp.loanofficer = pc.loanofficer
        JOIN appusers AS a
            ON a.id = lp.loanofficer
SET     pc.loanofficer = a.fullname;