Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: July 20, 2017 - 3:05 pm UTC

Last updated: July 25, 2017 - 7:27 am UTC

Version: Oracle 10

Viewed 1000+ times

You Asked

Primary Key: Parent: ID
Foreign Key: ParentID in Child Table
1 to many relationship

Parent: ID, FldValueIWant
Child ParentID, FldToUpdate
Child ParentID, FldToUpdate
Child ParentID, FldToUpdate
Child ParentID, FldToUpdate

MS Access/VBA accepts SQL UPDATE query with INNER JOIN (1 to MANY)

MS Access SQL
UPDATE ChildTable B INNER JOIN ParentTable A
ON B.ParentID = A.ID
SET B.FldToUpdate = A.FldValueIWant;

It updates all records that match (very easy)


Oracle sends a non-key “error” as the 1 to many relation between the tables is ambiguous and not allowed in UPDATE

Oracle most-likely can do this in Stored Procedure
1. Store in Memvar Parent ID, FldValueIWant from 1st ROW
2. Update Childtable with FldValueIWant WHERE parentID = ID
3. Go to next Parent ROW and repeat from 1 until End-of-Table.

Question is: Is there an SQL statement that can do this?

and Connor said...

Check out the MERGE command. You can do stuff like:

merge into CHILD c
using ( select blah, blah, blah from PARENT ) p
on ( c.key_col = p.key_col ) 
when matched then
update
 set ....


https://docs.oracle.com/database/122/SQLRF/MERGE.htm#SQLRF01606

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review of Update -- 1 to many table relationship

Joseph Giallombardo, July 24, 2017 - 10:23 pm UTC

Thanks, that's great.
I am converting MS ACCESS/ SQL...to Oracle...so far so good.
This was a tricky one because the MS Access environment is "pretty loose". When there is a 1 to many link, it just makes a 2nd "dummy record"...and the uses the last matching value....


I did find code that joined my table to a select query with rownum=1 and then a second select that used:

WHERE EXISTS
SELECT 1 FROM T1.Field = T2.Field

rownum = 1 always would give 1 row and it was confirmed in the WHERE EXISTS clause

It worked and gave me the first value of the 1 row.
But MERGE looks very good. The issue however is that the child table has more than one value...which is sort of a problem...like which one is correct...? Turns out the code was there to do the update but the value was never used...go figure...thanks for your help. I am new to oracle and loving it after many years of MS ACCESS dev. My first opportunity to work in the environment. Love it! Thanks for your help! Great answer
Connor McDonald
July 25, 2017 - 7:27 am UTC

Glad you're enjoying Oracle.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library