Updating oracle table from a join
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.
We are going to update column DESCRIPTION and PICTURE in table Categories_Test by using data in table Categories, based on data in the common column CATEGORY_ID. Update data in a column in table A based on a common column in table B. Update data in two columns in table A based on a common column in table B. Conditionally update data in table A based on a common column in table B. No portion may be reproduced without my written permission.
The update query below shows that the PICTURE column is updated by looking up the same ID value in CATEGORY_ID column in table Categories_Test and Categories. If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword. Here we only want to update PICTURE column in Categories_Test table where the data in Category_Name column is Seafood in table Categories. Software and hardware names mentioned on this site are registered trademarks of their respective companies.
Let’s say this is how STUDENT_N looks before it is merged with STUDENT: merge into student a 2 using 3 (select id, name, score 4 from student_n) b 5 on (= b.id) 6 when matched then 7 update set = 8 , a.score = b.score 9 when not matched then 10 insert (a.id, a.name, a.score) 11 values (b.id, b.name, b.score); 5 rows merged.
select * from student; ID NAME SCORE ---------- --------------- ---------- 1 Jack 540 2 Rose 3 William 650 4 Caledon 620 5 Fabrizio 600 6 Thomas 7 Ruth 690 11 Brock 705 10 Lizzy 9 Wallace 600 8 Spicer 620 11 rows selected. But it does not work with a merge query (probably because of the update part): “Missing IN or OUT parameter at index:: 1” Would would you advise to retrieve the value of a field (typically an id) of the row that has been inserted or updated?
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.
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. In sum, accounting wants to update job 103's invoice amount to 0 and job 104's invoice amount to 0, while leaving all other amounts the same.
My first attempt, which was incorrect, was this: 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.
This is typically the case when you have to synchronize a table periodically with data from another source (table/view/query).Tags: Adult Dating, affair dating, sex dating