asked on

Oracle SQL update enhancement query

Dear Experts,

I have a table1 containing Sales data for all times
Month | Customer | Product | Quantity | Revenue

I have another staging table contain data for specific interval of months:
Month | Customer | Product | Quantity | Revenue

I want to update the Revenue & Quantity to be 0 in table1 for all the rows which are in the same months interval of staging table & not exist in staging table

each row is identified by Month, Customer & product

I tried the following solution bu unfortunatly ot takes more than half an hour how do I can enhance it ? or provide a better solution

                  update CPDATA.table1 V 
                      SET V.KF_QUANITY = 0 , V.KF_REVENUE = 0 Where ROW_ID in  
                       (
                              Select  ROW_ID FROM CPDATA.STAGINING T Where MONTH >=  StartInterval AND MONTH <= EndInterval  AND
                                   NOT EXISTS   
                                          ( SELECT * FROM CPDATA.STAGINING S  WHERE T.MONTH =S.MONTH  AND T.CUSTOMER = S.CUSTOMER
                                                 AND nvl(T.PRODUCT,'p')  = nvl(S.PRODUCT,'p')  )                                                 
                        );

Open in new window

Sujith

Your update will not update anything. Can you be more clear about what exactly are you trying to achieve. With some sample data and expected output?

Your update
>> Select ROW_ID FROM CPDATA.STAGINING T Where MONTH >= StartInterval AND MONTH <= EndInterval
Gets the data from CPDATA.STAGINING T for a specific interval

>> NOT EXISTS
( SELECT * FROM CPDATA.STAGINING S WHERE T.MONTH =S.MONTH AND T.CUSTOMER = S.CUSTOMER
AND nvl(T.PRODUCT,'p') = nvl(S.PRODUCT,'p') )

Checks whether those rows DOESNT exist in CPDATA.STAGINING S

Which will NEVER be true.

So no rowids will be returned and no update.

Mark Geerlings

1. What is "ROW_ID" in your query? Every Oracle table has a "ROWID" (no underscore in the middle) but I don't see how Oracle's rowid helps you in this case.

2. Are both of your tables indexed on these columns: month, customer, product?

3. Using nvl on an indexed column value prevents Oracle from using an index on that column.

Jamil Muammar

ASKER

Hi Mark

1. an auto increment key
2. the index only on ROW_ID
3. I use nvl because this field may contain null value

Thanks

Jamil Muammar

ASKER

Hi Sujith.

I'm uploading data to sales table, one of my scenario if this prodcut in same month & same customer exist in sales & not exist in staging table
to update the Revenue & Quantity in Sales to be zero

example
Sales Table before update
Month | Customer | Product | Quantity | Revenue
201803 cust1 prod1 5 100
201804 cust1 prod 1 8 56

Staging Table
Month | Customer | Product | Quantity | Revenue
201804 Cust1 prod1 8 56

Sales Table After the Query
Month | Customer | Product | Quantity | Revenue
201803 cust1 prod1 0 0
201804 cust1 prod 1 8 56

I hope it's clear now

Thanks in advance

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jamil Muammar

ASKER

Hi Sujith,

thanks for the answer, how do I make the update only in Month interval in Sales table ?

Thanks

Sujith

>> how do I make the update only in Month interval in Sales table ?
Do yo mean add a filter condition?
You can add a filter to limit what rows you are selecting/updating/merging

MERGE INTO sales x
USING (
    select   a.month, a.customer, a.product
            ,case when g.month is null then 0 else a.revenue end revenue
    from    sales a left outer join staging g
    on      a.month = g.month
    and     a.customer = g.customer
    and     nvl(a.product,'p') = nvl(g.product,'p')
    WHERE   a.month between '201803' and '201804'
) y
ON (
    x.month = y.month and x.customer = y.customer and nvl(x.product,'p') = nvl(y.product,'p')
)
WHEN MATCHED THEN
UPDATE
SET x.revenue = y.revenue;

Open in new window