I have 2 table
Table 1: EnrollmentTransaction
EnrollmentId | TransactionId
5 | 1
5 | 2
6 | 3
7 | 2
8 | 3
8 | 2
8 | 1
Table 2: EnrollmentDetail (TransactionId column added newly with default value 1 since it’s not null column)
EnrollmentId | ParameterId| TransactionId
5 | 1 | 1
6 | 8 | 1
7 | 9 | 1
7 | 6 | 1
8 | 8 | 1
By referring to Table 1, I would like to insert if there is no entry or update if there is an entry based on EnrollmentId.
For example,
Scenario 1: EnrollmentId 5 has 2 entries in table 1 whereas, table 2 has only 1 entry and I must insert one more row with values 5, 1, 2 in table 2.
Sceanrio 2: EnrollmentId 6 has only 1 entry in table 1 and I need to update with value 6, 8 , 3 in table 2.
Is there any possibility to write a single query to achieve this?
Note: Oracle 19 is the database.