title: Updating rows in a view from a complex select statement
body: <p>I have been working on a query that identifies an issue with the data in my database:</p>
  <pre><code>SELECT t1.*
  FROM [DailyTaskHours] t1
  INNER JOIN (
      SELECT ActivityDate
          ,taskId
          ,EnteredBy
      FROM [DailyTaskHours]
      WHERE hours != 0
      GROUP BY EnteredBy
          ,taskId
          ,ActivityDate
      HAVING COUNT(*) &gt; 1
      ) t2 ON (
          t1.ActivityDate = t2.ActivityDate
          AND t1.taskId = t2.taskId
          AND t1.EnteredBy = t2.EnteredBy
          AND t1.Hours != 0
          )
  ORDER BY ActivityDate
  </code></pre>
  <p>What this does is find duplicate hours booked for the same person on the same task on the same day:</p>
  <p><img src="https://i.stack.imgur.com/8HvZ6.png" alt="enter image description here" /></p>
  <p>Now that I found the issues I want to correct them with an UPDATE. I want the duplicate activity that was created earlier than the other to move the value from Hours to doubleBookedHours and for Hours to be zeroed out. Secondly, I want the more recent row's DoubleBookedFLag column to be updated to 1.</p>
  <p>How can I achieve this?</p>