Hi,
version 11.2.0.3
Wonder how best the following can be achieved?
Have a merge statement and when matched we execute an update on target table if finalised column on target not already set to TRUE.
requirements below.
Have input rows need to be procesesd into target table.
- If no row found for pk on tgt write new row on tgt
- If row found on tgt for pk but not yet finalised i.e. column on target called finalised != TRUE, then update existing attributes on tgt
- If row found but existing trg.finalised column = TRUE, write a new row on target with new pk and leave existing row untouched.
Currently using a merge to cater for 1 and 3 but would like to add option for 2 inside same merge if possible but doesnt seem.
Can this all be achieved inside the same merge?
Is there better way of achieving it.
currently below o.k.
WHEN MATCHED
THEN UPDATE
SET tgt."COL_A"= SRC.COL_A
WHERE tgt."Finalised" != 'TRUE'
WHEN NOT MATCHED
THEN INSERT...
Would like to add extra bit below but when try add doesn't like it.
WHEN MATCHED
THEN INSERT...
WHERE tgt."Finalised" = TRUE
Thanks