Update a table with data from another table

We received data from another system exported in an excel file and want that into our database. So we create a temporary table and have to update our data with the data in the temp table.

Oracle has added direct joins to update and delete statements in Oracle 23C. For users working with SQL Server it will now feel more intuitive:

update EMPLOYEES emp
  set emp.comm = tdep.COMM
 from temp_depts tdep 
 where tdep.DEPT_NO = emp.DEPTNO;        

This is much less code compared to what we do now.

Lets say we have an employees table.

ENAME        COMM    DEPTNO 
_________ _______ _________ 
SMITH                    20 
ALLEN         300        30 
WARD          500        30 
JONES                    20 
MARTIN       1400        30 
BLAKE                    30 
CLARK                    10 
SCOTT                    20 
KING                     10 
TURNER          0        30 
ADAMS                    20 
JAMES                    30 
FORD                     20 
MILLER                   10         

And we have the temp_table with a new commision structure.

DEPT_NO    COMM 
__________ _______ 
        10    1000 
        20    2000         

One common mistake we make is that we use an update with a subquery.

update EMPLOYEES emp
  set emp.comm = (select tdep.COMM
                    from temp_depts tdep
                  where tdep.DEPT_NO = emp.DEPTNO);        

What would happen in this instance is that employees in departments that where not in the temp table will get updated to null. That's because the subquery returns null:

NAME        COMM    DEPTNO 
_________ _______ _________ 
SMITH        2000        20 
ALLEN                    30 
WARD                     30 
JONES        2000        20 
MARTIN                   30 
BLAKE                    30 
CLARK        1000        10 
SCOTT        2000        20 
KING         1000        10 
TURNER                   30 
ADAMS        2000        20 
JAMES                    30 
FORD         2000        20 
MILLER       1000        10        

To fix this there should be a where clause to only update where there is a match between the employees table and the temp table.

update EMPLOYEES emp
  set emp.comm = (select tdep.COMM
                    from temp_depts tdep
                  where tdep.DEPT_NO = emp.DEPTNO)
where exists (select 'exist'
                from temp_depts tdep
               where tdep.DEPT_NO = emp.DEPTNO);        

ENAME        COMM    DEPTNO 
_________ _______ _________ 
SMITH        2000        20 
ALLEN         300        30 
WARD          500        30 
JONES        2000        20 
MARTIN       1400        30 
BLAKE                    30 
CLARK        1000        10 
SCOTT        2000        20 
KING         1000        10 
TURNER          0        30 
ADAMS        2000        20 
JAMES                    30 
FORD         2000        20 
MILLER       1000        10        

There is another solution that I always seem to forget and that is the MERGE statement.

merge into EMPLOYEES emp
using
(select *
  from temp_depts) tdep on (tdep.DEPT_NO = emp.DEPTNO)
when matched then update set
emp.comm = tdep.COMM;        

That brings us back to where we started that all this can now be done in less code in Oracle 23C with direct joins.

update EMPLOYEES emp
  set emp.comm = tdep.COMM
 from temp_depts tdep 
 where tdep.DEPT_NO = emp.DEPTNO;        

When you upgrade your Oracle database to 23C how much time do you think you will unlearn the old ways of working and use the new improvements?

#oracle #oracle23c #join #codingbookworm