{"id":107,"date":"2009-01-29T13:04:43","date_gmt":"2009-01-29T18:04:43","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2009\/01\/29\/updating-oracle-table-with-data-from-multiple-tables-using-subquery\/"},"modified":"2009-01-29T13:04:43","modified_gmt":"2009-01-29T18:04:43","slug":"updating-oracle-table-with-data-from-multiple-tables-using-subquery","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2009\/01\/29\/updating-oracle-table-with-data-from-multiple-tables-using-subquery\/","title":{"rendered":"Updating Oracle table with data from multiple tables using subquery"},"content":{"rendered":"

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.<\/p>\n\n\n
\n\n\n\n\n\n\n\n\n
Master Table<\/th>\n<\/tr>\n
Job Number *<\/td>\nJob Type<\/td>\nInvoice Amount<\/td>\n<\/tr>\n
101<\/td>\nInstallation<\/td>\n1000<\/td>\n<\/tr>\n
102<\/td>\nInstallation<\/td>\n1000<\/td>\n<\/tr>\n
103<\/td>\nMaintenance<\/td>\n500<\/td>\n<\/tr>\n
104<\/td>\nRepair<\/td>\n400<\/td>\n<\/tr>\n
105<\/td>\nInstallation<\/td>\n3000<\/td>\n<\/tr>\n<\/table>\n<\/td>\n\n\n\n\n\n\n
Data From Accounting<\/th>\n<\/tr>\n
Job Number *<\/td>\nUpdated Invoice Amount<\/td>\n<\/tr>\n
103<\/td>\n400<\/td>\n<\/tr>\n
104<\/td>\n600<\/td>\n<\/tr>\n<\/table>\n<\/td>\n<\/tr>\n<\/table>\n

* Primary key<\/small><\/p>\n

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.<\/p>\n

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<\/b>, was this:<\/p>\n

\n-- Incorrect example!\nupdate master_table m\nset m.invoice_amount=(\n\tselect a.updated_invoice_amount from data_from_accounting\n\twhere m.job_number=a.job_number\n);\n<\/pre>\n
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:<\/p>\n
\n-- Better example, but still inefficient\nupdate master_table m\nset m.invoice_amount=(\n\tselect a.updated_invoice_amount from data_from_accounting a\n\twhere m.job_number=a.job_number\n)\nwhere m.job_number in(\n\tselect a2.job_number from data_from_accounting a2\n\twhere m.job_number=a2.job_number\n);\n<\/pre>\n
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.<\/p>\n
Finally, I came up with a solution that works very efficiently, even with my large 1,000,000-record table.<\/p>\n
\n-- Best practice\nupdate (\n\tselect m.invoice_amount, a.updated_invoice_amount\n\tfrom master_table m, data_from_accounting a\n\twhere m.job_number=a.job_number\n)\nset m.invoice_amount=a.updated_invoice_amount;\n<\/pre>\n
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.<\/p>\n
\n-- Primary key\nalter table master_table add (\n  constraint pk_master_table primary key (job_number)\n);\n\n-- Unique index\ncreate unique index idx_data_from_accounting on data_from_accounting (job_number);\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-107","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=107"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/107\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}