I am trying to:
How can step 3 be done? I can do step 1 and 2 already as I have already updated or inserted the items that are pulled from the cursor.
Here is some example data:
There are three source tables where it is updated by an external party. My objective is to take these three sources of data and merge it into a singular table.
SOURCE TABLES
ITEM_DESCRIPTION
ITEM_ID | TYPE
0 | Kitchen
1 | Bath
ITEM_MANIFEST
ITEM_ID | ORIGIN
0 | USA
1 | CHINA
ITEM_PRICE
ITEM_ID | PRICE
0 | 3.99
1 | 2.99
DESTINATION TABLE
STORE_INVENTORY
ITEM_ID | TYPE | ORIGIN | PRICE
0 | Kitchen | CHINA | 3.99
8 | Bath | USA | 2.99
So after I execute the SQL Procedure the source tables to be upserted into STORE_INVENTORY and have any items that are not in the source table to have the price nulled. I know how to do the Upsert portion, but I do not know how to NULL out the price for items that already exist in the STORE_INVENTORY table.
Expected Output
STORE_INVENTORY
0 | Kitchen | USA | 3.99
1 | Bath | China | 2.99
8 | Bath | USA | NULL
I know from this example that I could just look for if the ITEM_ID is not in the any of the SOURCE_TABLES then null it out, but what if there were more complicated logic that determined what items to pull from the source tables? (Which is why I want to use a cursor) I trying to figure out if I can compare against what is NOT IN the cursor?
As suspected, this just requires a single MERGE statement:
create table item_description
as
select 0 item_id, 'Kitchen' type from dual union all
select 1 item_id, 'Bath' type from dual;
create table item_manifest
as
select 0 item_id, 'USA' origin from dual union all
select 1 item_id, 'CHINA' origin from dual;
create table item_price
as
select 0 item_id, 3.99 price from dual union all
select 1 item_id, 2.99 price from dual;
create table store_inventory
as
select 0 item_id, 'Kitchen' type, 'CHINA' origin, 3.99 price from dual union all
select 8 item_id, 'Bath' type, 'USA' origin, 2.99 price from dual;
select * from store_inventory;
ITEM_ID TYPE ORIGIN PRICE
---------- ------- ------ ----------
0 Kitchen CHINA 3.99
8 Bath USA 2.99
merge into store_inventory tgt
using (select coalesce(id.item_id, si.item_id) item_id,
coalesce(id.type, si.type) type,
coalesce(im.origin, si.origin) origin,
ip.price
from item_description id
inner join item_manifest im on (id.item_id = im.item_id)
inner join item_price ip on (id.item_id = ip.item_id)
full outer join store_inventory si on (si.item_id = id.item_id)) src
on (src.item_id = tgt.item_id)
when matched then
update set tgt.type = src.type,
tgt.origin = src.origin,
tgt.price = src.price
when not matched then
insert (tgt.item_id, tgt.type, tgt.origin, tgt.price)
values (src.item_id, src.type, src.origin, src.price);
commit;
select * from store_inventory;
ITEM_ID TYPE ORIGIN PRICE
---------- ------- ------ ----------
0 Kitchen USA 3.99
8 Bath USA
1 Bath CHINA 2.99
All I did was first of all write the select statement that produced the joined list of item_id, type, origin and price from the source tables.
Once I had that, it was then just a case of doing a full outer join onto the store_inventory, to make sure that all rows, be they new or existing rows would be displayed.
Then I did a coalesce on the item_id, type and origin columns, so that if the row exists only in the store_inventory table, we'd still see those details populated, but I kept the price from the source rows - which if it didn't exist would be null.
Then it's just a matter of merging the results of that query back into the store_inventory table.
If you are ever in doubt about how to merge/update/insert rows into a table, see if you can first write a select statement that procduces the results that you are after. It's often just a matter of taking that statement and plugging it into the relevant merge/update/insert statement.