Merge not respecting ON in SQL

Question

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');

Caius Jard · Accepted Answer · 2019-01-17T10:33:14.417

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

Thanks for the comment Caius. The #tmpappoint has 3 groups effectively. Regarding, Optional and Required, and each of these has an ID and Type associated to it as their own columns. In my #tmpappoint table, Regarding and Required work fine because it just so happens that all of the IDs for these are O, C or U and the corresponding IDs all exist in the Officers table which is needed as a Primary key so there are no issues, but it also just so happens to be that all of the Optionaltypes are L's, and any L IDs will be something totally different to Officer codes (dealt with in later Merges) — , Jan 17 '19 at 10:18
Ultimately, it could be a mixed bag of IDs and Types in my #tmp table, but each Merge statement (you're only seeing a snapshot here) should deal with each combination. — , Jan 17 '19 at 10:19
Don't forget that you can "transform" the data in #tmpappoint by way of a query (like where i filter it to only 'L') to make your tmpappoint data compatible with the join condition.. something like `USING ( SELECT case when code = 'L' then badges.badge_number ELSE officer_code END as optionalid FROM #tmpappoint t JOIN badges ON badges.officer_code = t.officer_code...` - in this case, if it's a L, then we put e.g. their badge number in as the officer code, otherwise we put the officer_code in.. We're preprocessing the data to make the join ON conditions work out — Caius Jard, Jan 17 '19 at 10:20

Merge not respecting ON in SQL

1 Answers1