2

I think I've been looking at this statement way too long. Can anyone tell me why the update portion of this statement isn't updating? I think that by testing with or and != on the columns, this should invoke a cause for updates only when needed--instead, I seem to have unintentionally created a condition ..where the update is never needed!

Here are 4 things to know prior to looking at the statement:
1. The data in the temp table #Facilities is populated with the most current data from multiple sources. This data contains bot new (insert) and changed (update) data.
2. The contents of #Facilities has been verified to contain the data, prior to executing the Merge statement.
3. The insert portion of the merge statement succeeds.
4. Although the update portion of the statement does not update changed data, the statement does not fail.

MERGE INTO PhoneMaster AS facilitymaster
 USING #Facilities as facilitynew
   ON facilitymaster.facilityid = facilitynew.facilityid
    AND facilitymaster.siteid = facilitynew.siteid
     WHEN MATCHED AND 
     (
       facilitymaster.User__bID != facilitynew.User__bID
    or facilitymaster.Email__baddress != facilitynew.Email__baddress
    or facilitymaster.Facility__bName != facilitynew.Facility__bName
    or facilitymaster.Division != facilitynew.Division
    or facilitymaster.District != facilitynew.District
      ) THEN
    UPDATE SET
     facilitymaster.User__bID = facilitynew.User__bID,
     facilitymaster.Email__baddress = facilitynew.Email__baddress,
     facilitymaster.Facility__bName = facilitynew.Facility__bName,
     facilitymaster.Division = facilitynew.Division,
     facilitymaster.District = facilitynew.District
     WHEN NOT MATCHED THEN
  INSERT
  ( User__bID,
    Email__baddress,
    Facility__bName,
    Division,
    District,
            FacilityId,
    SiteId

  )
  VALUES
  (
    facilitynew.User__bID,
    facilitynew.Email__baddress,
    facilitynew.Facility__bName,
    facilitynew.Division,
    facilitynew.District,
            facilitynew.FacilityId,
    facilitynew.SiteId

 );
Jamie Eltringham
  • 810
  • 3
  • 16
  • 25
plditallo
  • 701
  • 2
  • 13
  • 31
  • Why do you need MERGE for this? What do you think it buys you (other than more convoluted and difficult-to-debug syntax)? Seems you could achieve this much simpler with an `UPDATE WHERE EXISTS / INSERT WHERE NOT EXISTS` pattern. – Aaron Bertrand May 13 '13 at 19:44
  • 1
    @Aaron, why not use merge for something like this? I don't see the syntax as necessarily any harder. There's just a gap here between what I think should work and why it isn't. Any ideas on why this logic isn't working? – plditallo May 13 '13 at 19:51
  • I don't have the energy to look at the logic but here's why I don't use MERGE (in addition to the impossible-to-memorize syntax): http://www.sqlperformance.com/2013/02/t-sql-queries/another-merge-bug (please read the whole post). – Aaron Bertrand May 13 '13 at 19:54

1 Answers1

1

One thing I've noticed is that your insert statment does not populate FacilityID or SiteID. The other suggestion I would make would be to comment out your 'AND' clause on the WHEN MATCHED, adding in back in a line at a time until it fails. I use MERGE all the time and it is VERY fast and efficient.

Peager
  • 11
  • 1
  • thanks for responding in favor of merge! :) sorry about the insert statement with the missing attributes--they were there, it was an unfortunate cut 'n paste job--in truth this MERGE statement contains many,many more attributes. I cut them out to get rid of the noise -- to focus on the logic. – plditallo May 13 '13 at 21:07
  • I'll try your process, I know (from trying) that if I eliminate the AND clause and just do a straight update using the site/facility keys, all is well--although it updates EVERY matched record--whether it needs it or not. Which is my alternative for a table with less than 100k rows. it would be nice to understand what is wrong with my logic though, since I wouldn't be able to apply the same solution to a table with millions of rows. This logic flaw is probably something spectacularly silly! :) – plditallo May 13 '13 at 22:05