Search the Site

Sponsors

bottom corner

Updating Oracle table with data from multiple tables using subquery

In Oracle, when you need to update values of certain records from one table, and the value and the records to update are determined by a second table, you can use a subquery in the update statement to achieve the updating efficiently.

This page is filed under keyword(s): oracle.

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 TypeInvoice Amount
101Installation1000
102Installation1000
103Maintenance500
104Repair400
105Installation3000
Data From Accounting
Job Number *Updated Invoice Amount
103400
104600

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

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 29 Jan 2009

bottom corner