CODEWITHSUNDEEP
sqloracle-database
mani.gkn
mani.gkn

Reputation: 85

Update table based on the condition

I need to update the staging table based on the type if ZMD2 is present then update the records else update PNTP records.

enter image description here

UPDATE ITEMS_STAGING SET TYPE=b.TYPE,VALUE=b.VALUE
FROM ITEMS_STAGING a,ITEMS b
WHERE a.PARENT=b.PARENT

In the above statement I need to pick only ZMD2 records for the same parent if exists if not PNTP records. I tried to do UNION for the ITEMS it dint help.

Staging table Output:

enter image description here

Kindly help.

Thanks

Upvotes: 1

Views: 62

Answers (2)

Popeye
Popeye

Reputation: 35900

You need to use analytical function row_number which will group the rows by parent column to give them numbers and then we will take only one record from each group to update staging table using merge statement as following:

MERGE INTO ITEM_STAGING M
  USING (
   SELECT T.*, 
          ROW_NUMBER() OVER(PARTITION BY T.PARENT ORDER BY T.TYPE DESC) RN
     FROM ITEMS T
  ) 
  ON (M.PARENT = T.PARENT AND T.RN = 1)
  WHEN MATCHED THEN
  UPDATE SET M.TYPE = T.TYPE AND M.VALUE = T.VALUE;

Cheers!!

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You may try below query - 

SELECT *
FROM (SELECT IS.*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TYPE DESC) RN
      FROM ITEMS_STAGING)
WHERE RN = 1;

I am not sure what you want to update in this table.

Upvotes: 0

Related Questions