Is there a way to the following with one update in PL/SQL?-CodePudding

This is what we have in the table (CNTRCT_NTFYUSER) now:

CNTRCT_ID	VERSION_NBR	NOTIFY_TYPE	OPRID
456	1	A	ExistingUser2
789	1	A	ExistingUser3
123	2	A	ExistingUser1

We need to insert some additional rows for all CNTRCT_IDs with NOTIFY_TYPE of 'E' and 'R'.

Insert 2 rows for all 'ExistingUser#'with NOTIFY_TYPE of 'E' and 'R'
Insert 2 additional rows with for each CNTRCT_ID with the OPRID of 'NewUser1' and 'NewUser2' for each Contract with NOTIFY_TYPE of 'E' and 'R'.

CNTRCT_ID	VERSION_NBR	NOTIFY_TYPE	OPRID
456	1	A	ExistingUser2
456	1	E	ExistingUser2
456	1	R	ExistingUser2
456	1	E	NewUser1
456	1	R	NewUser1
456	1	E	NewUser2
456	1	R	NewUser2
789	1	A	ExistingUser3
789	1	E	ExistingUser3
789	1	R	ExistingUser3
789	1	E	NewUser1
789	1	R	NewUser1
789	1	E	NewUser2
789	1	R	NewUser2
123	2	A	ExistingUser1
123	2	E	ExistingUser1
123	2	R	ExistingUser1
123	2	E	NewUser1
123	2	R	NewUser1
123	2	E	NewUser2
123	2	R	NewUser2

CodePudding user response：

You can generate:

"all 'ExistingUser#'with NOTIFY_TYPE of 'E' and 'R'", using a CROSS JOIN between your <CNTRCT_ID, VERSION_NBR, OPRID> combinations and your possible "NOTIFY_TYPE" values (E and R)
"for each CNTRCT_ID with the OPRID of 'NewUser1' and 'NewUser2' for each Contract with NOTIFY_TYPE of 'E' and 'R'", using two CROSS JOIN operations on both NOTIFY_TYPE in [E, R] and OPRID in [NewUser1, NewUser2]

Hence, use an INSERT statement before your SELECT statement.

INSERT INTO CNTRCT_NTFYUSER

SELECT CNTRCT_ID, VERSION_NBR, nt.NOTIFY_TYPE, OPRID
FROM CNTRCT_NTFYUSER
CROSS JOIN (SELECT 'E' AS NOTIFY_TYPE FROM DUAL UNION ALL SELECT 'R' FROM DUAL) nt

UNION ALL

SELECT CNTRCT_ID, VERSION_NBR, nt.NOTIFY_TYPE, oid.OPRID
FROM CNTRCT_NTFYUSER
CROSS JOIN (SELECT 'E' AS NOTIFY_TYPE FROM DUAL UNION ALL SELECT 'R' FROM DUAL) nt
CROSS JOIN (SELECT 'NewUser1' AS OPRID FROM DUAL UNION ALL SELECT 'NewUser2' FROM DUAL) oid;

Check the Oracle demo here.

CodePudding user response：

Try this INSERT statement:

INSERT INTO CNTRCT_NTFYUSER
Select      c1.CNTRCT_ID, c1.VERSION_NBR, x.NOTIFY_TYPE, x.OPRID
From        CNTRCT_NTFYUSER c1
Inner Join  (  SELECT er.NOTIFY_TYPE, usr.OPRID 
               FROM ( (Select 'E' "NOTIFY_TYPE" From Dual UNION ALL Select 'R' "NOTIFY_TYPE" From Dual) er
                      Inner Join (Select 'NewUser1' "OPRID" From Dual UNION ALL Select 'NewUser2' From Dual) usr ON(1 = 1)
                    )
            ) x ON(1 = 1)

Where The Inner joined query's result

SELECT er.NOTIFY_TYPE, usr.OPRID 
               FROM ( (Select 'E' "NOTIFY_TYPE" From Dual UNION ALL Select 'R' "NOTIFY_TYPE" From Dual) er
                      Inner Join (Select 'NewUser1' "OPRID" From Dual UNION ALL Select 'NewUser2' From Dual) usr ON(1 = 1)
                    )

NOTIFY_TYPE OPRID  
----------- --------
E           NewUser1 
E           NewUser2 
R           NewUser1 
R           NewUser2

...containes both the new types and new users.

So, with your initial data like here:

Select * From CNTRCT_NTFYUSER ORDER BY CNTRCT_ID, VERSION_NBR, NOTIFY_TYPE

 CNTRCT_ID VERSION_NBR NOTIFY_TYPE                    OPRID                        
---------- ----------- ------------------------------ ------------------------------
       123           2 A                              ExistingUser1                  
       456           1 A                              ExistingUser2                  
       789           1 A                              ExistingUser3

... the result would be:

Select * From CNTRCT_NTFYUSER ORDER BY CNTRCT_ID, VERSION_NBR, NOTIFY_TYPE

 CNTRCT_ID VERSION_NBR NOTIFY_TYPE                    OPRID                        
---------- ----------- ------------------------------ ------------------------------
       123           2 A                              ExistingUser1                  
       123           2 E                              NewUser1                       
       123           2 E                              NewUser2                       
       123           2 R                              NewUser1                       
       123           2 R                              NewUser2                       
       456           1 A                              ExistingUser2                  
       456           1 E                              NewUser1                       
       456           1 E                              NewUser2                       
       456           1 R                              NewUser1                       
       456           1 R                              NewUser2                       
       789           1 A                              ExistingUser3                  
       789           1 E                              NewUser2                       
       789           1 E                              NewUser1                       
       789           1 R                              NewUser1                       
       789           1 R                              NewUser2