Update TransactionId based on mapping table data

Question

I have 2 tables:

Table 1: ProductTransaction (PK is both the column)

Here, TransactionId value options will be 1 or 2 or 3 | 1 and 2 | 1, 2 and 3.

Only 3 combinations are possible. ProductId 5 has mapped 1 and 2 options. ProductId 6 has mapped to option 3. ProductId 8 has mapped to all 3 options (1,2 and 3).

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

Table 2: ProductRange (TransactionId column added newly with default value 1 since it's not null column) In this table PK are (ProductRangeId, ProductId and TransactionId )

ProductRangeId| ProductId  | TransactionId 
-------------+------------ +--------------
31            | 5          | 1
32            | 6          | 1
33            | 7          | 1
34            | 7          | 1
35            | 7          | 1
36            | 8          | 1
37            | 9          | 1
38            | 9          | 1

By referring to Table 1, I would like to update TransactionId in table 2.

Logic considerations to update TransactionId.

1. If the productId has single entry in Table 1, then check ProductId present in the table 2.

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

2. If the productId has multiple entry in Table 1 (Max 3 entries), then check ProductId present in the table 2.

• If there is no entry found table 2, then no action required.
• If there is entry found in table 2, then we need to consolidate TransactionsId from table1 and check what combination they fall into and update respective transactionId in Table 2 .

Only below 3 combination possible and Consolidation will happens only when multiple entry found in table 1 for the productId.

     If the productId mapped to 1 and 2, then the transactionId will be 4
     If the productId mapped to 1, 2 and 3, then the transactionId will be 5
     If the product mapped to 1 or 2 or 3, then the corresponding transactionId will be the value.

This is one time activity and it goes till production.

Final result will be like as below.

ProductRangeId| ProductId  | TransactionId 
-------------+------------ +--------------
31            | 5          | 4
32            | 6          | 3
33            | 7          | 4
34            | 7          | 4
35            | 7          | 4
36            | 8          | 5
37            | 8          | 5
38            | 9          | 1

Example:

1. ProductId 5 has 2 entries in table 1 with transactionIds of 1 and 2. So TransactionId value will be 4 for productId 5 in table 2
2. ProductId 6 has only 1 entry in table 1 with transactionId 3. So TransactionId value will be 3 for productId 6 in table 2.
3. ProductId 7 has 2 entries in table 1 with transactionIds of 1 and 2. So TransactionId value will be 4 for productId 7 in table 2.
4. ProductId 8 has 3 entries in table 1 with transactionIds of 1, 2 and 3. So TransactionId value will be 5 for productId 8 in table 2.
5. ProductId 9 has only 1 entry in table 1 with transactionId 1. So TransactionId value will be 1 for productId 9 in table 2.

Is there any possibility to write a single query to achieve this?

Note: Oracle 19 is the database.

Answer 1

You could use MERGE INTO using case expression to control the consolidated transaction id like here:

MERGE INTO PRODUCTRANGE p
USING ( Select Distinct t.PRODUCTID, 
               Case When Count(t.TRANSACTIONID) Over(Partition By t.PRODUCTID) = 3 Then 5
                    When Count(t.TRANSACTIONID) Over(Partition By t.PRODUCTID) = 2 Then 4
                    When Count(t.TRANSACTIONID) Over(Partition By t.PRODUCTID) = 1 Then t.TRANSACTIONID
               End "CONSOLIDATED_TRANSACTIONID"
       From   PRODUCTTRANSACTION t
     ) x ON(x.PRODUCTID = p.PRODUCTID)
  WHEN MATCHED THEN
       UPDATE SET p.TRANSACTIONID = x.CONSOLIDATED_TRANSACTIONID
WHERE p.TRANSACTIONID != x.CONSOLIDATED_TRANSACTIONID;

There is a typo in your data provided for second table (range id 37). In the initial table data there is product 9 and in final table data there is product 8. I took product 8.

/*    R e s u l t :
PRODUCTRANGEID  PRODUCTID  TRANSACTIONID
--------------  ---------  -------------
            31          5              4
            32          6              3
            33          7              4
            34          7              4
            35          7              4
            36          8              5
            37          8              5
            38          9              1    */

See the fiddle here.
Another fiddle checking IDs here.