CODEWITHSUNDEEP
sqloracle-databaseplsqldata-warehouse
user3808188
user3808188

Reputation: 577

Oracle: updating column IF ONLY it is not in condition of Merge statement

I'm having trouble with this Oracle Merge query.

    merge into NEW_DOCTORS NP
    USING OLD_DOCTORS MD
    on (MD.SSN=NP.SSN OR MD.NPI=NP.NPI OR MD.PIN=NP.PIN)
    WHEN MATCHED THEN
    UPDATE SET 
    NP.othervalues=MD.othervalues
    NP.PIN/SSN/NPI=MD.PIN/SSN/NPI (**update two of these three that did not return TRUE in the above ON condition)
    WHEN NOT MATCHED THEN
    insert(NP.SSN,NP.NPI,NP.PIN,NP.other_values)
    values(MD.SSN,MD.NPI,MD.PIN,MD.other_values)

Is this possible? Thanks!

EDIT: I'm asking because I read somewhere that fields that are in the ON condition can't be updated. However I'm not sure if the author was talking only in context of the field that evaluates true or for all fields. I'm hoping there's someone who might have an idea about this, as well as any workarounds.

Upvotes: 1

Views: 570

Answers (1)

Ryx5
Ryx5

Reputation: 1366

You can use CASE WHEN ... :

UPDATE SET 
NP.othervalues=MD.othervalues
,NP.PIN = CASE WHEN (MD.SSN=NP.SSN OR MD.NPI=NP.NPI) THEN MD.PIN ELSE NP.PIN END
,--add here the 2 others

This means, when you have MD.SSN=NP.SSN OR MD.NPI=NP.NPI true, then update NP.PIN with MD.PIN else let the same value NP.PIN

Or you can do 3 differents MERGE, so it will be more readable.

EDIT

Thinking about it, if you update only when this is not the same value (cause it will join only on same value) you can directly update them with the MD table value.

Do that :

NP.othervalues=MD.othervalues
,NP.PIN=MD.PIN
,NP.SSN=MD.SSN
,NP.NPI=MD.NPI

If they match, the update will keep same value, if not it will update the value.

Upvotes: 2

Related Questions