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
July 25, 2017 - 7:27 am UTC
Glad you're enjoying Oracle.