title: SQL Server delete rows not in source based on parameters
body: <p>I have a table that needs to be updated hourly based on weather forecasts coming in. The create table and merge works great and I have been using them for the last while now. </p>
  
  <pre><code>CREATE TABLE #WeatherHourly([Pk_Id][int],
                              [ObservationDateTime] [datetime],    
                              [EffectiveDateTime] [datetime],
                              [Value] [decimal](18, 4)
                             )
  
  MERGE INTO WeatherHourly AS TARGET 
  USING #WeatherHourly AS Source ON Target.Pk_Id = Source.Pk_Id 
                                 AND Target.EffectiveDateTime = Source.EffectiveDateTime 
  WHEN MATCHED THEN
     UPDATE SET Target.Value = Source.Value,
                Target.ObservationDateTime = Source.ObservationDateTime 
  WHEN NOT MATCHED BY TARGET THEN 
     INSERT ([Pk_Id], [ObservationDateTime], [EffectiveDateTime], [Value]) 
     VALUES (Source.Pk_Id, Source.ObservationDateTime, Source.EffectiveDateTime, Source.Value);
  </code></pre>
  
  <p>But what I've noticed is that sometimes new forecasts don't include old data that I have already written to the table. This means that some data is sticking around because nothing ever writes over/updates it in the merge statement. This data is really not valid anymore since it's not included in the newest forecast. Now just adding a 'not matched by source' in the merge would work, though it would delete all rows of history. I am keeping the history currently for other reasons and can not delete old rows,but only rows that are in WeatherHourly table and still relevant/(EffectiveDateTime within range from yesterday to any future date). The code below is what I've tried so far but it would delete all the history rows I don't want deleted. </p>
  
  <pre><code>Delete from WeatherHourly from WeatherHourly tRow 
  left JOIN #WeatherHourly t2 ON tRow.EffectiveDateTime = t2.EffectiveDateTime and   tRow.Pk_Id = t2.Pk_Id
  where t2.PK_Id is null
  </code></pre>
  
  <p>Any help would be appreciated.</p>
  
  <p>Edit for my new approach:
  I'm wondering about using a better select for my original merge to make the target more what I would like. </p>
  
  <pre><code>WITH    t2
  AS
  (
  SELECT  *
  FROM    WeatherHourly t
  WHERE   EXISTS
  (
  SELECT * FROM #WeatherHourly r WHERE t.EffectiveDateTime = r.EffectiveDateTime AND  t.Pk_Id = r.Pk_Id
  )
  )
  MERGE t2
  AS Target
  USING   #WeatherHourly
  AS Source
  ON Target.Pk_Id = Source.Pk_Id 
      AND Target.EffectiveDateTime = Source.EffectiveDateTime 
  WHEN MATCHED THEN 
      UPDATE SET Target.Value = Source.Value,Target.ObservationDateTime = Source.ObservationDateTime 
  WHEN NOT MATCHED BY TARGET THEN
      INSERT ([Pk_Id],[ObservationDateTime],[EffectiveDateTime],[Value]) 
      VALUES (SourcePk_Id,Source.ObservationDateTime,Source.EffectiveDateTime,Source.Value)
  WHEN NOT MATCHED BY SOURCE THEN 
      DELETE;
  </code></pre>
  
  <p>This seems to be doing what I wanted. Any critique would be helpful. Sorry to whomever is going to fix this code mess up.</p>