Hi,
We're using Oracle 12c.
I'm trying to update a table in our DEV DB from out PROD DB using MERGE.
I have it set up such that if there's a match on the primary key field, then the statement does an update. If there's no match on the primary key( meaning there's a primary key value on prod that's not in dev) then that means there's a new row in prod that's not in dev. So you would insert that new row into the dev table. In the where clause under the UPDATE SET section What I'm trying to say is to only do an update if any of the corresponding columns do not match between data bases. I'm using ‘OR’ to do this. I understand using ‘OR’ can be tricky and I want to make sure I'm stating this correctly.
Please chime in if any of you feel this statement will not work as I'm intending it to.
MERGE INTO sld_cltrl_fund target
USING sld_cltrl_fund@DEV_TO_PROD_LINK.NTRS.COM source
ON (target.I_CLTRL_FUND_ID = source.I_CLTRL_FUND_ID)
WHEN MATCHED
THEN
UPDATE SET
target.I_CLTRL_FUND = source.I_CLTRL_FUND,
target.N_CLTRL_FUND = source.N_CLTRL_FUND,
target.I_CLTRL_FUND_RPT = source.I_CLTRL_FUND_RPT
WHERE target.I_CLTRL_FUND <> source.I_CLTRL_FUND
OR target.N_CLTRL_FUND <> source.N_CLTRL_FUND
OR target.I_CLTRL_FUND_RPT <> source.I_CLTRL_FUND_RPT
WHEN NOT MATCHED
THEN
INSERT (target.I_CLTRL_FUND_ID,
target.I_CLTRL_FUND,
target.N_CLTRL_FUND,
target.I_CLTRL_FUND_RPT)
VALUES (source.I_CLTRL_FUND_ID,
source.I_CLTRL_FUND,
source.N_CLTRL_FUND,
source.I_CLTRL_FUND_RPT);