UPDATE the records in oracle SQL with joinings and where exists query

join;oracleupdate

I have the tables below and

  1. tempaddresstable
  2. account
  3. premise
  4. address

Here is the link of details(id of account = account_fk of premise)
(address_fk of premise = id of address)

So only goal is to update all the records info from tempaddresstable to address table

I have tried the below query but I am getting error

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

Error :

SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 – "single-row subquery returns more than one row"
*Cause:
*Action:

Details of the table

  1. tempaddresstable
  2. account
  3. premise
  4. address

tempaddresstable

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

account

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

premise

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

address

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

Best Answer

Usually, when you need to join multiple tables in an UPDATE, MERGE is the solution.

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;