我有三个表,分别称为联系人、APP_User和客户表。在这里,我想根据Contact_ID从我的联系人表中选择电子邮件作为App_User表中的外键,并且我想根据App_User_ID将所选电子邮件更新到我的Customer表中作为Customer表中的主键和App_User表的主键。
很明显:-我想在Contact.contact_ID = APP_User.Contact_ID
处获取电子邮件,并将获取的电子邮件更新为Customer.emial
,其中PP_User.App_User_Id = Customer.App_User_Id
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;
但结果是:
ORA-01427:单行子查询返回多行
以下查询必须返回多行,这是问题的原因:
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;
干杯!!