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

Question

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     |
+---------+----------+

Answer 1

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;

Answer 2

you have to take care with those renaming of tables. The error might be due to the inner query is not being filtered by the outer query. What does this mean: change the update for : UPDATE ADDRESS AD_outer. And inside the inner query, something like this:

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

Without any guarantees, but give it a try.