Merge not respecting ON in SQL

I have a set of Merge statements I am running in Advantage SQL, but I am finding some aren't working. I know why, but not sure where in my script it's making it go wrong. See the script below. In the ON statement, the part where it says optionaltype in ('O', 'C', 'U') is where it's going wrong I believe. In the #tmpappoint table, there are no optionaltypes of O, C or U, but rather L, but it seems we aren't disregarding this statement, and we are looking to bring through the optionalid from #tmpappoint regardless, despite this being an optionaltype of L. Unfortunately, the optionalids of L are not officer codes that can be found in another table, so therefore I get the error. What can I do in this statement to disregard anything that's not O, C or U as I can't fathom this one?

merge pcevactor ev
using #tmpappoint ap
on (ev.evid = ap.evid and
ev.officer = ap.optionalid and
optionaltype in ('O', 'C', 'U'))
when matched then
update set
roles=trim(replace (roles,'C',''))+'C'
when not matched then
insert
(
evid,
officer, 
readstatus,
flagstatus,
roles)
values
(ap.evid,
ap.optionalid,
'U',
'-',
'C');

Solution

The "problem" you have with a merge is that it will do either one thing, or another. If the join condition works out, records are updated in pcevactor. If the join doesnt work out, records are inserted into pcevactor

You seem to be saying that your #tmpappoint table is currently full of type 'L', so the join condition will NOT work out and the INSERT will always be used.

If you want the UPDATE to occasionally be used (i.e. if ev.evid = ap.evid and ev.officer = ap.optionalid works out) in the case of 'L' in the #tmpappoint then you'll have to put 'L' into the IN as well:

optionaltype in ('L', 'O', 'C', 'U'))

If #tmpappoint contains all sorts of codes and you only want to do the 'L' ones right now, refine the #tmpappoint table at source:

merge pcevactor ev
using (Select * from #tmpappoint where optionaltype = 'L') ap
on (... and
optionaltype in ('L', 'O', 'C', 'U'))
...

Remember that the table or query after the USING in a merge statement is the set of data that drives the merge; only those rows in ap (be it a query or a table) will be searched for in pcevactor and updates or inserts will be made as necessary. Rows not mentioned in ap are not touched

By extension of the above:

If #tmpappoint contains all sorts of codes and you DON'T want to do anything other than O, C, U ones right now, refine the #tmpappoint table at source (and remove the merge condition as it is no longer needed):

merge pcevactor ev
using (Select * from #tmpappoint where optionaltype in ('O', 'C', 'U')) ap
on (ev.evid = ap.evid and
    ev.officer = ap.optionalid
)
when matched then

If your data in #tmppappoint is deficient in some way, like when optionaltype of 'L' means it's the offcier's badge_number rather than their employee_number that needs to be used as the ID, you can manipulate the data by way of a query before trying to merge it:

MERGE ...
USING ( 
  SELECT 
    ...,
    --let's change what we put in optionalid depending on the code 
    CASE
      WHEN code = 'L' THEN badges.badge_number 
      ELSE t.employee_number
    END as optionalid,
    ...
  FROM
    #tmpappoint t 
    JOIN 
    badges ON badges.officer_code = t.officer_code
  ...

In this case, if it's an L, then we put e.g. their badge number in as the ID, otherwise we put the officer_code in.. We're preprocessing the data to make the merge's ON conditions work out