CODEWITHSUNDEEP
sqloracle-database
Sivaraj Thavamani
Sivaraj Thavamani

Reputation: 139

Oracle : How to update multiple columns from different table?

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

Upvotes: 7

Views: 50983

Answers (3)

Jenna Leaf
Jenna Leaf

Reputation: 2472

updating one table by another table - the basic format is

--ORACLE
update tableX t set (t.fldA, t.fldB) =
(select fldA, fldB from table_B where ID ='X')
where t.ID = 'Y'

Upvotes: 1

user5683823
user5683823

Reputation:

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;

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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

Upvotes: 10

Related Questions