Combination Update/Insert query?

• She Through Whom All Data Flows

  Ten Centuries

  Points: 1209

  More actions

  April 27, 2009 at 4:23 pm

  #220608

  Quite a few places in my Access db's, I've used left-join update queries, as a way to accomplish the following:

  If the records in question are already there, update them.

  If they aren't there, add them.

  So, now I'm trying to do this in SQL server. In the example below, what I want to do is:

  If noun and fcstdate already exist in Pastforecast, just update the fcstqty to match that in PastForecast_Append1.

  If noun and fcstdate don't exist in PastForecast, append from PastForecast_Append1.

  However, when I run this, I don't get any errors, but nothing gets addedto Pastforecast.

  Does this approach just not work in SQL? What would be the correct approach?

  UPDATE Pastforecast

  SET PastForecast.Noun = pastforecast_Append1.noun,

  PastForecast.dDate = pastforecast_append1.fcstdate,

  PastForecast.Fcst = pastforecast_append1.fcstqty,

  PastForecast.EntryDate = getdate()

  from

  PastForecast_Append1 LEFT JOIN PastForecast ON (PastForecast_Append1.FcstDate=PastForecast.dDate)

  AND (PastForecast_Append1.Noun=PastForecast.Noun)

  [font="Comic Sans MS"]She Through Whom All Data Flows[/font]

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 28, 2009 at 6:14 am

  #985006

  In SQL Server you need to explicitly insert new rows. So in your case I'd do it like this:

  UPDATE Pastforecast

  SET PastForecast.Noun = pastforecast_Append1.noun,

  PastForecast.dDate = pastforecast_append1.fcstdate,

  PastForecast.Fcst = pastforecast_append1.fcstqty,

  PastForecast.EntryDate = getdate()

  from

  PastForecast_Append1 INNER JOIN PastForecast ON (PastForecast_Append1.FcstDate=PastForecast.dDate)

  AND (PastForecast_Append1.Noun=PastForecast.Noun)

  Insert Into PastForecast

  (

  Noun,

  dDate,

  Fcst,

  EntryDate

  )

  Select

  PastForecast_Append1.Noun,

  PastForecast_Append1.dDate,

  PastForecast_Append1.Fcst,

  PastForecast_Append1.EntryDate

  from

  PastForecast_Append1 LEFT JOIN

  PastForecast ON

  (PastForecast_Append1.FcstDate=PastForecast.dDate) AND

  (PastForecast_Append1.Noun=PastForecast.Noun)

  Where

  PastForecast.Noun Is Null

  Note the Inner Join on the update as I want ONLY matches there and the LEFT JOIN WHERE IS NULL for the INSERT to only insert new rows.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question