使用外键关系更新从另一个表中选择的电子邮件

Question

UPDATE CRM.CRM_CUSTOMER_USER 
  SET EMAIL = (SELECT EMAIL 
               FROM QA29.ST_CONTACT 
                 INNER JOIN QA29.ST_APP_USER ON QA29.ST_CONTACT.CONTACT_ID = 129) 
WHERE APP_USER_ID = 120; 

Answer 1

内部查询中缺少联接条件。

以下查询必须返回多行，这是问题的原因：

SELECT
    EMAIL
FROM
    QA29.ST_CONTACT
    INNER JOIN QA29.ST_APP_USER ON QA29.ST_CONTACT.CONTACT_ID = 129;

您必须使用有效的联接条件将其限制为单个记录，如下所示：

UPDATE CRM.CRM_CUSTOMER_USER
SET
    EMAIL = (
        SELECT
            EMAIL
        FROM
            QA29.ST_CONTACT
            INNER JOIN QA29.ST_APP_USER 
            ON QA29.ST_CONTACT.APP_USER_ID = QA29.ST_APP_USER.APP_USER_ID -- OR SOMETHING LIKE THIS
            AND QA29.ST_CONTACT.CONTACT_ID = 129
    )
WHERE
    APP_USER_ID = 120;