Abhishek Sharma
asked on
Update multiple tables
Hi Team,
Need suggestion for one of my oracle problem.
I have 1 main table AVAILABLE_STG having columns IDENTIFY_1 and PROC_CD. This table DOES NOT have SKU column.
Other table REFERENCE_IDENTIFIER is having columns IDENTIFY_1 and SKU. It is 1-1 mapping. 1 IDENTIFY_1 will have 1 SKU only.
Now i need to check if value of IDENTIFY_1 from main table AVAILABLE_STG is present in REFERENCE_IDENTIFIER.
If it is present then i need to take some other columns from main table AVAILABLE_STG and insert into MAIN_SUCCSESS table along with SKU information against each IDENTIFY_1.
Once this is done i need to make PROC_CD as 'S' in main table AVAILABLE_STG
If SKU not found, then i need to update other table MAIN_FAILURE with some column from main table AVAILABLE_STG and also update the PROC_CD as 'N' AVAILABLE_STG .
How to achieve this. i am stuck at this from long time. Any pointers would be helpful.
Need suggestion for one of my oracle problem.
I have 1 main table AVAILABLE_STG having columns IDENTIFY_1 and PROC_CD. This table DOES NOT have SKU column.
Other table REFERENCE_IDENTIFIER is having columns IDENTIFY_1 and SKU. It is 1-1 mapping. 1 IDENTIFY_1 will have 1 SKU only.
Now i need to check if value of IDENTIFY_1 from main table AVAILABLE_STG is present in REFERENCE_IDENTIFIER.
If it is present then i need to take some other columns from main table AVAILABLE_STG and insert into MAIN_SUCCSESS table along with SKU information against each IDENTIFY_1.
Once this is done i need to make PROC_CD as 'S' in main table AVAILABLE_STG
If SKU not found, then i need to update other table MAIN_FAILURE with some column from main table AVAILABLE_STG and also update the PROC_CD as 'N' AVAILABLE_STG .
How to achieve this. i am stuck at this from long time. Any pointers would be helpful.
ASKER
do you require insert statements or just data?
I'm not a 100% clear on your needs either, but if its just a view you are after.
SELECT a.IDENTIFY_1, b.SKU FROM AVAILABLE_STG as a JOIN REFERENCE_IDENTIFIER as b
ON (a.IDENTIFY_1 = b.IDENTIFY_1)
>>do you require insert statements or just data?
I would prefer insert statements and table definitions but as long as you provide data I can turn into insert statements, I can work with it.
Don't forget the expected results from the data you provide so we can tell when we get it right.
I would prefer insert statements and table definitions but as long as you provide data I can turn into insert statements, I can work with it.
Don't forget the expected results from the data you provide so we can tell when we get it right.
I would usually use a PL\SQL procedure to accomplish tasks like this. The procedure would include:
1. A cursor loop to fetch all of the columns you need (plus the rowid) from AVAILABLE_STG.
2. Four additional cursors or SQL statements for each row retrieved by cursor #1:
a. A second cursor from REFERENCE_IDENTIFIER to fetch the SKU for each IDENTIFY_1 value from AVAILABLE_STG.
b. An insert into MAIN_SUCCESS if a matching SKU value was found.
c. An insert into MAIN_FAILURE if no matching SKU was found.
d. An update of AVAILABLE_STG using the rowid fetched in cursor #1 to set the PROC_CD to S or N using either a DECODE or CASE based on whether cursor #2 found a matching SKU or not.
1. A cursor loop to fetch all of the columns you need (plus the rowid) from AVAILABLE_STG.
2. Four additional cursors or SQL statements for each row retrieved by cursor #1:
a. A second cursor from REFERENCE_IDENTIFIER to fetch the SKU for each IDENTIFY_1 value from AVAILABLE_STG.
b. An insert into MAIN_SUCCESS if a matching SKU value was found.
c. An insert into MAIN_FAILURE if no matching SKU was found.
d. An update of AVAILABLE_STG using the rowid fetched in cursor #1 to set the PROC_CD to S or N using either a DECODE or CASE based on whether cursor #2 found a matching SKU or not.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F
There are examples in the docs and online. If you can post sample data and expected results (no images please), we can create a test case and post tested SQL.