Creating a merge that contains an IF condition

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  September 14, 2020 at 8:14 pm

  Go to Answer

  #3788418

  Hi,

  want to merge two tables on two columns, partid and serialid.

  but then I want to check if the purchasedate also match, if it matches I leave it alone, otherwise I update the date and source. i having having issues with the if part.

  MERGE [dbo].[tableone] T
  USING [dbo].tabletwo S
  ON (S.PARTID = T.PART_ID AND S.SERIAL = T.SERIAL)
  WHEN MATCHED
  --i would like to check here if the dates also match
  -- do nothing
  -- if they dont then update
  THEN UPDATE SET
  T.DATE = S.DATE...
  ---and then otherwise insert.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  September 14, 2020 at 9:38 pm

  Answer

  #3788441

  Have you tried something like this? The HOLDLOCK helps avoid potential concurrency issues.

  MERGE [dbo].[tableone] WITH (HOLDLOCK) T
  USING [dbo].tabletwo S
  ON (S.PARTID = T.PART_ID AND S.SERIAL = T.SERIAL)
  WHEN MATCHED AND (S.DATE <> T.DATE or T.DATE IS NULL)
  THEN UPDATE SET
  T.DATE = S.DATE...
  ---and then otherwise insert.

  ---

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  September 16, 2020 at 2:57 pm

  #3788868

  thanks