Updating oracle table from a join datinghost ru
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?
Example use case: I have a database that contains a table of contacts (contact) and table of e-mail addresses (email), joined on = email.contact_id.
I just found out that Example Conglomerate acquired Osric Publishing’s Oracle consulting business, and so I need to update my contacts database so that all of the Oracle consultants who had @e-mail addresses now have @e-mail addresses.
In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records.
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.
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.
Note the use of the alias CAT for the CATEGORY table in the FROM clause.
The previous example showed an inner join specified in the FROM clause of an UPDATE statement.
Search for updating oracle table from a join:
Should any right be infringed, it is totally unintentional.