i have this scenario, for which i have written the sql statements as shown below

This query will return combination of ITEM_OBJID, S_ITEMI, S_IDCNG from Object_Data and Part_list table.

SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM SN.OBJECT_DATA AS OBJECT_DATA INNER JOIN SN.PART_LIST AS PART_LIST ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD'

3. For each S_ITEMI, S_IDCNG from above query we will check if this combination exists in PM. If below query returns the values then the combination does exists.

3.a We query for ITM_ID, EC_MC_ID from PM using Join on three tables AVNTMITEM, AVENTACI, AVNTEC as below.

SELECT ITM_ID, EC_MC_ID FROM SN.AVNTMITEM AVNTMITEM JOIN SN.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN SN.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = ‘S_ITEMI’ AND EC_MC_ID = ‘IDCNG’


3.b If we get ITM_ID, EC_MC_ID from above query we will update the Q_PMSYNC as 'Y' in ERE Object_Data table

UPDATE SN.OBJECT_DATA SET Q_PMSYNC = 'Y' WHERE ITEM_OBJID = ‘ITEM_OBJID’ AND S_IDCNG = ‘S_IDCNG’


I tried doing whole of the work using a single sql statement which got complex and i landed no where, this task can also be accomplished usinga Stored procedure.

can any one help me writing a single sql statement for this task as it will be faster to execute as the records may be in lacs.

Thanks,
Prashant

You should be able to achieve this with a single MERGE statement; check the manual for syntax.