title: Update Column with row number
body: <p>I have an temp table table in SQL Server stored Procedure with a Id and date column. The table is already populated with dates and I want to be able to add a sequence number in the id column based on the dates in ascending order</p>
  
  <p>I have tried the following </p>
  
  <pre><code>UPDATE #Weeks 
  SET Id = (
            SELECT ROW_NUMBER() OVER (ORDER BY WeekDate ASC) AS Id 
            FROM #Weeks AS DATA);
  </code></pre>
  
  <p>and get this error.</p>
  
  <p>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  
  <p>Any help is appreciated.</p>
  
  <p>This worked for me.....</p>
  
  <p>UPDATE #Weeks 
  SET Id = tab.Id
  FROM #Weeks w
       INNER JOIN (
                   SELECT WeekDate,
                          ROW_NUMBER() OVER (ORDER BY WeekDate ASC) AS Id 
                   FROM #Weeks ) Tab ON w.WeekDate = Tab.WeekDate </p>