oracle sql - merge table with nested table as column

Question

I have 2 tables and one nested table:

1.stores data about products which include following columns:

     ITEM - product id(key)
     STORE - store id(key)
     PRICE
     NORMAL_PRICE
     DISCOUNTS - nested table with info about discounts include columns:
             PromotionId(key)
             PromotionDescription
             PromotionEndDate
             MinQty
             DiscountedPrice
             DiscountedPricePerMida

2- temp table with new discounts include columns:

PROMOTIONID(key)
PRODUCTID(key)
PROMOTIONDESCRIPTION
PROMOTIONENDDATE
MINQTY
DISCOUNTEDPRICE
DISCOUNTEDPRICEPERMIDA

What i need to do is merge table 2 into table 1 - if no match insert else ignore (when match is: product id matching in table 1 and 2 and for this product sub table PROMOTIONID match PROMOTIONID from table 2)

This is where I got so far and I have difficulty with nested part - ON clause and Insert clause

MERGE INTO PRICES P
USING(SELECT * FROM TMP_PROMO)T
ON(P.ITEM=T.PRODUCTID AND P.STORE=50 AND P.DISCOUNTS.PROMOTIONID=T.PROMOTIONID)
WHEN NOT MATCHED THEN INSERT (P.DISCOUNTS)
       VALUES(T.PROMOTIONID,
              T.PROMOTIONDESCRIPTION,
              T.PROMOTIONENDDATE,
              T.MINQTY,
              T.DISCOUNTEDPRICE,
              T.DISCOUNTEDPRICEPERMIDA);

I know that this is wrong but I can't find anywhere how to do it

example: Prices table:

row1(1,50,...,nested_table[(11,...),(12,...)])

row2(2,50,...,nested_table[(10,...),(12,...)])

new promo table:

(15,1,...)

(11,1,...)

new promo with id 15 will be added to row1 and row2

and promo with id 11 will not be added

Please help, thanks

Just unnest the nested table using a query like this: `SELECT ITEM, x.* FROM table1 t1, TABLE( t1.DISCOUNTS ) x` and use it as a subquery in MERGE statement, just like any other ordinary query. — krokodilko, May 04 '16 at 15:53

score 0 · Answer 1 · answered May 04 '16 at 16:34

What you intend to do is not realy a MERGE. You are adding a new promotion in each record that doesn't contain it.

Below is an answer how yu would approach it if you would use not a nested table but a conventional child table.

Setup (simplified to a minimum)

create table ITEM
(ITEM_ID NUMBER PRIMARY KEY);

create table ITEM_PROMO
(ITEM_ID NUMBER REFERENCES ITEM(ITEM_ID),
PROMO_ID NUMBER);

create table TMP_PROMO
(PROMO_ID NUMBER);

insert into ITEM values (1);
insert into ITEM values (2);

insert into ITEM_PROMO values (1,11);
insert into ITEM_PROMO values (1,12);
insert into ITEM_PROMO values (2,10);
insert into ITEM_PROMO values (2,12);

insert into TMP_PROMO values (15);
insert into TMP_PROMO values (11);

commit;

The first thing you need to find is which promotions are missing for an item. Use a cross join to get all combination and constrain those promotions that EXISTS for a particular ITEM_ID:

select ITEM.ITEM_ID, TMP_PROMO.PROMO_ID 
from ITEM cross join TMP_PROMO
where NOT EXISTS (select NULL from ITEM_PROMO where ITEM_ID = ITEM.ITEM_ID and  PROMO_ID = TMP_PROMO.PROMO_ID)
;

This gives as expected

   ITEM_ID   PROMO_ID
---------- ----------
         2         11 
         1         15 
         2         15

Now simple add those new promotions

INSERT INTO ITEM_PROMO
select ITEM.ITEM_ID, TMP_PROMO.PROMO_ID 
from ITEM cross join TMP_PROMO
where NOT EXISTS (select NULL from ITEM_PROMO where ITEM_ID = ITEM.ITEM_ID and  PROMO_ID = TMP_PROMO.PROMO_ID)
;

This should give you a hint how to approach while using nested tables (or how to change the DB design:)

oracle sql - merge table with nested table as column

1 Answers1