Updating Oracle table with data from multiple tables using subquery

I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I’m going to use comes from the accounting department. Let us set up the scenario below.

Master Table Job Number * Job Type Invoice Amount 101 Installation 1000 102 Installation 1000 103 Maintenance 500 104 Repair 400 105 Installation 3000

Data From Accounting Job Number * Updated Invoice Amount 103 400 104 600

* Primary key

In sum, accounting wants to update job 103’s invoice amount to $400 and job 104’s invoice amount to $600, while leaving all other amounts the same. In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records. Also, this scenario’s master table has only 5 records; my real master table has close to 1,000,000 records. Thus, we need an update statement that is efficient.

Because Oracle does not allow multiple tables within the same update statement, I know at least one subquery will be needed. My first attempt, which was incorrect, was this:

-- Incorrect example!
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting
	where m.job_number=a.job_number
);

The problem with this update statement was that I realized it would update all 5 records instead of just the 2 records accounting wanted to update; this would result in 103 and 104 being updated to the updated amounts, but all other amounts wiped out to null value. To remedy that, we could throw in a where clause to the update statement, as below:

-- Better example, but still inefficient
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting a
	where m.job_number=a.job_number
)
where m.job_number in(
	select a2.job_number from data_from_accounting a2
	where m.job_number=a2.job_number
);

This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.

Finally, I came up with a solution that works very efficiently, even with my large 1,000,000-record table.

-- Best practice
update (
	select m.invoice_amount, a.updated_invoice_amount
	from master_table m, data_from_accounting a
	where m.job_number=a.job_number
)
set m.invoice_amount=a.updated_invoice_amount;

Note that the job number field in both tables in this example scenario are both primary keys. In order for the “best practice” update statement to work, they must be either primary keys or unique indexes, otherwise the query will fail with the error message “ORA-01779: Cannot modify a column which maps to a non key-preserved table”. As an aside, to create primary key or unique indexes, follow the examples below.

-- Primary key
alter table master_table add (
  constraint pk_master_table primary key (job_number)
);

-- Unique index
create unique index idx_data_from_accounting on data_from_accounting (job_number);