使用聯接和 where exists 查詢更新 oracle SQL 中的記錄

我有下表和

1. 臨時地址表
2. 帳戶
3. 前提
4. 地址

這是詳細資訊的連結（帳戶id = 前提的account_fk）（前提的address_fk = 地址的id）

所以唯一的目標是將所有記錄資訊從臨時地址表更新到地址表

我已嘗試以下查詢，但出現錯誤

UPDATE ADDRESS AD
SET AD.ADDRESS1 =
 (SELECT (B.TEMPADDRESS1)
  FROM tempAddressTable B,
       ADDRESS AD
  WHERE AD.ID =
      (SELECT (P.ADDRESS_FK)
       FROM ACCOUNT A
       LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
       LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
       LEFT JOIN ADDRESS AD ON AD.ID = P.ADDRESS_FK
       WHERE A.EXT_REF = B.TEMPEXTREF ))
WHERE EXISTS
   (SELECT 1
    FROM tempAddressTable B
    WHERE AD.ID =
        (SELECT DISTINCT max(P.ADDRESS_FK)
         FROM ACCOUNT A
         LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
         LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
         WHERE A.EXT_REF = B.TEMPEXTREF ));

錯誤 ：

SQL 錯誤：ORA-01427：單行子查詢返回多行 01427。00000 -“單行子查詢返回多行”

*原因：

*操作：

表的詳細資訊

1. 臨時地址表
2. 帳戶
3. 前提
4. 地址

臨時地址表

+------------+----------------------------+
| TEMPEXTREF | TEMPADDRESS1               |
+------------+----------------------------+
| 34         | 101 DRCSG117432RES RD TEST |
| 35         | 102 DRCSG117               |
| 36         | 100 DRCSG117432RES RD      |
+------------+----------------------------+

帳戶

+---------+---------+
| ID      | EXT_REF |
+---------+---------+
| 1041261 | 34      |
| 1041262 | 35      |
| 1041263 | 36      |
+---------+---------+

前提

+---------+------------+------------+
| ID      | ACCOUNT_FK | ADDRESS_FK |
+---------+------------+------------+
| 1044610 | 1041261    | 1041502    |
| 1044611 | 1041262    | 1041503    |
| 1044612 | 1041263    | 1041504    |
+---------+------------+------------+

地址

+---------+----------+
| ID      | ADDRESS1 |
+---------+----------+
| 1041502 | test     |
| 1041503 | test     |
| 1041504 | test     |
+---------+----------+

通常，當您需要在一個中加入多個表時UPDATE，MERGE是解決方案。

merge into address a1 using
(
  select
   p.address_fk,
   ta2.tempaddress1
 from
   premise p 
   join account ac on (ac.id = p.account_fk)
   join tempaddresstable ta2  on (ac.ext_ref = ta2.tempextref)
) ta1
on (a1.id = ta1.address_fk)
when matched then update set a1.address1 = ta1.tempaddress1;

你必須小心那些重命名表。該錯誤可能是由於外部查詢未過濾內部查詢。這是什麼意思：更改 : 的更新UPDATE ADDRESS AD_outer。在內部查詢中，是這樣的：

(SELECT (P.ADDRESS_FK)
       FROM ACCOUNT A
       LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
       LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
       LEFT JOIN ADDRESS AD ON AD.ID = P.ADDRESS_FK
       WHERE A.EXT_REF = B.TEMPEXTREF
and AD.ID = AD_OUTER.ID ))

沒有任何保證，但試一試。

引用自：https://dba.stackexchange.com/questions/174447