mysql - 使用 SELECT 值更新 SQL 列

Question

我在 prequals_created 表中有一个名为 loanofficer 的列，其中列出了与该信贷员相对应的数字。我想更新此列以使用信贷员姓名而不是编号，该编号位于名为 appusers 的表中，其中 fullname 与 匹配loanofficer id.我试图通过首先使用 SELECT 通过使用名为 loan_pairing 的“中间”表来生成信贷员姓名和 ID 成对的表来执行此操作。但是，我的语法有些问题。这是我的代码:

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 配对的信贷员的姓名。

我的语法到底有什么问题？

Answer 1

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