INSERT and UPDATE the Values into the table based on condition

Question

I have 2 tables:

Table 1: EnrollmentTransaction (PK is both the column)

EnrollmentId | TransactionId
-------------+--------------
5            | 1
5            | 2
6            | 3
7            | 2
7            | 3
8            | 3
8            | 2
8            | 1
9            | 1

Table 2: EnrollmentDetail (TransactionId column added newly with default value 1 since it's not null column) In this table PK are (EnrollmentId, ParameterId, SVCId and TransactionId )

EnrollmentId | ParameterId| SVCId| SVCValueId| TransactionId 
-------------+------------+------+-----------+--------------
5            | 1          | 57   | 21        | 1
6            | 8          | 58   | 24        | 1
7            | 9          | 57   | 21        | 1
7            | 6          | 58   | 29        | 1
8            | 8          | 57   | 21        | 1

By referring to Table 1, I would like to insert if there is no combination of(EnrollmentId , TransactionId ) 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 row with values 5, 1, 57, 21, 2 in table 2. I should not make any changes of the table 1 existing combination row (5, 1, 57, 21, 1). since it matches the combination of Enrollment Id and TrnasactionId.

Scenario 2: EnrollmentId = 6 has only 1 entry in both table 1 and table 2, but TransactionId is different in both the table. So I now have to refer table 1 for TransactionId and update in table 2. mainly TrasnactionId will be final value to update from the table 1. The final values of this row will be 6, 8, 58, 24 and 3.

Scenario 3: EnrollmentId = 9 has only 1 entry in table 1 and no entry found in the table 2. so I should not insert/update of this combination.

Logic considerations.

1. When there is single entry in Table 1, then check in Table 2 if there is a EnrollmentId present.

• If there is no entry found table 2, then no action required.
• If there is mulitple entry found in table 2, then update corresponding trasnactionID in table 2 for those records.

2. When there is more than one entry present in Table 1, then check in Table 2 if there is a EnrollmentId present.

• If there is no entry found table 2, then no action required.
• If there is a single entry found in table 2, then update/insert records in table 2 to match the combination of TransactionId and EnrollmentId along with values of ParameterId, SVCID and SVCValueId. Like EnrollmentId 5 in the final result table.
• If there is mulitple entry found in table 2, Each entry in table 2 must match with table 1 combination. Like enrollmentId 7 in final result table.

Note: Posible combination in table 1 for each enrollmentId will be 3 values. i.e 1, 2 or 3.

This is one time activity and it goes till production.

Final result will be like as below.

EnrollmentId | ParameterId| SVCId| SVCValueId| TransactionId  
-------------+------------+---------------------------------
5            | 1          | 57   | 21        | 1
5            | 1          | 57   | 21        | 2
6            | 8          | 58   | 24        | 3
7            | 9          | 57   | 21        | 2
7            | 6          | 58   | 29        | 2
7            | 9          | 57   | 21        | 3
7            | 6          | 58   | 29        | 3
8            | 8          | 57   | 21        | 1
8            | 8          | 57   | 21        | 2
8            | 8          | 57   | 21        | 3

Is there any possibility to write a single query to achieve this? I have to achieve both insert and update in the same query.

Note: Oracle 19 is the database.

Answer 1

You can use a MERGE statement and merge rows based on prioritising matching transactionid values:

MERGE INTO enrollmentdetail dst
USING (
SELECT t.enrollmentid,
       t.transactionid,
       d.parameterid,
       d.svcid,
       d.svcvalueid,
       d.rowid AS rid,
       ROW_NUMBER() OVER (
         PARTITION BY d.ROWID
         ORDER BY CASE WHEN d.transactionid = t.transactionid THEN 0 ELSE 1 END
       ) AS rn
FROM   enrollmenttransaction t
       INNER JOIN enrollmentdetail d
       ON t.enrollmentid = d.enrollmentid
) src
ON (dst.ROWID = src.rid AND src.rn = 1)
WHEN MATCHED THEN
  UPDATE
  SET parameterid   = src.parameterid,
      transactionid = src.transactionid,
      svcid         = src.svcid,
      svcvalueid    = src.svcvalueid
WHEN NOT MATCHED THEN
  INSERT (enrollmentid, transactionid, parameterid, svcid, svcvalueid)
  VALUES (src.enrollmentid, src.transactionid, src.parameterid, src.svcid, src.svcvalueid);

Which, for the sample data:

CREATE TABLE EnrollmentTransaction (EnrollmentId, TransactionId) AS
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 5, 2 FROM DUAL UNION ALL
SELECT 6, 3 FROM DUAL UNION ALL
SELECT 7, 2 FROM DUAL UNION ALL
SELECT 7, 3 FROM DUAL UNION ALL
SELECT 8, 3 FROM DUAL UNION ALL
SELECT 8, 2 FROM DUAL UNION ALL
SELECT 8, 1 FROM DUAL UNION ALL
SELECT 9, 1 FROM DUAL;

CREATE TABLE EnrollmentDetail (
  EnrollmentId, ParameterId, TransactionId, SVCId, SVCValueID
) AS
SELECT 5, 1, 1, 57, 21 FROM DUAL UNION ALL
SELECT 6, 8, 1, 58, 24 FROM DUAL UNION ALL
SELECT 7, 9, 1, 57, 21 FROM DUAL UNION ALL
SELECT 7, 6, 1, 58, 29 FROM DUAL UNION ALL
SELECT 8, 8, 1, 57, 21 FROM DUAL;

Then after the MERGE the table contains:

ENROLLMENTID	PARAMETERID	TRANSACTIONID	SVCID	SVCVALUEID
5	1	1	57	21
5	1	2	57	21
6	8	3	58	24
7	9	2	57	21
7	6	2	58	29
7	9	3	57	21
7	6	3	58	29
8	8	1	57	21
8	8	2	57	21
8	8	3	57	21

Note: this gets the expected answer for your sample data; please validate the logic for other scenarios that are not present in your sample data as your logic is complicated and not easily expressed using SQL.

fiddle