Oracle : How to update multiple columns from different table?

Question

I am using oracle database and have a situations to update fields from some other tables. My issue is it is updating all the records instead of specified conditions. For example, I am trying to update perm_address and temp_address in EMPLOYEE table from ADDRESS table. Right now, I am using below query. But, it is updating all the records.

UPDATE EMPLOYEE EMP
     SET (EMP.PERM_ADDRESS, EMP.TEMP_ADDRESS) =
          (SELECT ADDR.PERM_ADDR,ADDR.TEMP_ADDR
           FROM ADDRESS ADDR
           WHERE ADDR.ID=EMP.ADDRESS_ID
          );

In Oracle how to handle this situations? Normally, how to handle the update from multiple table into source table?

Thanks in advance....

Answer 1

Add a WHERE clause to update only matching records:

UPDATE EMPLOYEE EMP
     SET (EMP.PERM_ADDRESS, EMP.TEMP_ADDRESS) =
          (SELECT ADDR.PERM_ADDR, ADDR.TEMP_ADDR
           FROM ADDRESS ADDR
           WHERE ADDR.ID = EMP.ADDRESS_ID
          )
     WHERE EXISTS (SELECT 1 FROM ADDRESS ADDR WHERE ADDR.ID = EMP.ADDRESS_ID);

Answer 2

Updating a table with data from another table is often simpler using the MERGE statement. https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Something like this:

merge into employee emp
   using address addr
   on (addr.id = emp.address_id)
when matched 
   then update 
        set emp.perm_address = addr.perm_addr, 
            emp.temp_address = addr.temp_addr;