Hello All,
I am using Toad for Oracle 10. I have a MERGE INTO Process that updates tbl_requisition based on FK - fk_allotment_id that equals parent table tbl_allotment PK - pk_allotment_id. Both tables have create, update and deletes triggers. The process is executed when a Apply Changes/update button is clicked from tbl_allotment. So, all the record data from tbl_allotment updates tbl_requisition record if the fk and pk keys are equal. My problem is if a record is updated within tbl_requisition. Now the record from tbl_requisition is different from tbl_allotment. If any value is updated from tbl_allotment for the matching pk_allotment_id = fk_allotment_id record from tbl_requisition. tbl_allotment record data will override the updated value within tbl_requisition. I would like to only update the values that were updated/changed and are not equal from tbl_allotment to tbl_requisition. Can anyone assist me with this?
Begin
MERGE INTO tbl_requisition req
USING tbl_allotment alt
ON (req.fk_allotment_id = alt.pk_allotment_id)
WHEN MATCHED THEN
UPDATE SET
req.FK_JOBCODE_ID = alt.FK_JOBCODE_ID,
req.FK_JOBCODE_DESCR = alt.FK_JOBCODE_DESCR,
req.FK_JOBCODE_PAYRANGE = alt.FK_JOBCODE_PAYRANGE,
req.FK_PAY_RANGE_LOW_YEARLY = alt.FK_PAY_RANGE_LOW_YEARLY,
req.FK_DEPARTMENT_ID = alt.FK_DEPARTMENT_ID,
req.FK_DIVISION_ID = alt.FK_DIVISION_ID,
req.FK_NUMBER_OF_POSITIONS = alt.NUMBER_OF_POSITIONS,
req.FK_DEPARTMENT_NAME = alt.FK_DEPARTMENT_NAME,
req.FK_DIVISION_NAME = alt.FK_DIVISION_NAME,
req.REPORT_UNDER = alt.REPORT_UNDER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No data found');
End;
Thanks for reading this thread and I hope someone can provide some assistance. If the create tables or anything is needed that is not a problem to provide.