title: Stored procedure to update rows if exists
body: <p>I have a stored procedure that I am running to insert values if they don't exist and update if they do.  I have a table where the data is coming from</p>
  
  <pre><code>Table1
  Name (nvarchar)
  Data (int)
  Timestamp (datetime)
  </code></pre>
  
  <p>The data looks like this</p>
  
  <pre><code>Name1       5       2016-11-16 09:46:40.490
  Name2       10      2016-11-16 09:48:35.240
  Name1       7       2016-11-16 09:35:24.350
  Name2       8       2016-11-15 02:27:44.670
  </code></pre>
  
  <p>I am trying to insert the names into a new table, average out the integers, and group by day.  Here is the second table</p>
  
  <pre><code>Table2
  Name (nvarchar)
  Data (int)
  Timestamp (date)
  </code></pre>
  
  <p>The data looks like this.  Name1 was same day so it was averaged.  Name2 was different days so they were not averaged.</p>
  
  <pre><code>Name1      6       2016-11-16 00:00:00.000
  Name2      10      2016-11-16 00:00:00.000
  Name2      8       2016-11-15 00:00:00.000
  </code></pre>
  
  <p>I converted the timestamp to date so it would be easier to combine on day.  My stored procedure looks like this</p>
  
  <pre><code>IF NOT EXISTS (SELECT t.Name, t.Timestamp
      FROM Table2 t
      JOIN Table1 a
      ON t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)
      GROUP BY t.Name, t.Timestamp)
  
  INSERT INTO Table2 (Name, Timestamp, Data)
  SELECT 
      Name,
      CAST(Timestamp AS DATE) as Date,
      AVG(Data) as Average_Data
  FROM Table1
  GROUP BY CAST(Timestamp AS DATE), Name
  
  ELSE
  UPDATE Table1
  SET
  WHERE
  </code></pre>
  
  <p>So I first time it runs, there is no issue.  The values are added and grouped correctly.  However, the second time it runs, it always inserts.</p>
  
  <p>My update statement had looked something like this before I deleted it</p>
  
  <pre><code>SET Name = Name, Timestamp = Timestamp, Data = Date
  WHERE Name = Name, Timestamp = Timestamp
  </code></pre>
  
  <p>I know this is not right but it never seems to even hit the Update as the Insert always runs.  If I run the Select in the If Not Exists, I see data and it should see it as exists.</p>
  
  <p>I guess I need help cleaning up the If Not Exists and creating a working Update statement.</p>
  
  <p>EDIT:</p>
  
  <p>Updated code below</p>
  
  <pre><code>IF EXISTS(SELECT Name, Timestamp FROM Table2)
  UPDATE 
      Table2
  SET 
      Name = a.Name,
      Timestamp = CONVERT(date, a.Timestamp),
      Data = AVG(a.Data)
  FROM
      Table2 t
  INNER JOIN
      Table1 a
  ON t.Name = a.Name
  WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)
  
  ELSE
      INSERT INTO Table2 (Name, Timestamp, Data)
          SELECT 
              Name,
              CAST(Timestamp AS DATE) as Date,
              AVG(Data) as Average_Data
          FROM Table1
          GROUP BY CAST(Timestamp AS DATE), Name
  END
  </code></pre>