Comment by ycom13__

Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this

  ;WITH cte AS(SELECT   
        ROW_NUMBER() OVER(PARTITION BY SomeVal   
                  ORDER BY SomeDate DESC) AS row,  
                * FROM SomeTable)

  UPDATE cte  
  SET id =  CASE WHEN row = 1 THEN 1 ELSE 0 END

Here is the DDL and DML in case you want to play around with this

  CREATE TABLE SomeTable(id int,SomeVal char(1),SomeDate date)
  INSERT SomeTable 
  SELECT null,'A','20110101'
  UNION ALL
  SELECT null,'A','20100101'
  UNION ALL
  SELECT null,'A','20090101'
  UNION ALL
  SELECT null,'B','20110101'
  UNION ALL
  SELECT null,'B','20100101'
  UNION ALL
  SELECT null,'C','20110101'
  UNION ALL
  SELECT null,'C','20100101'
  UNION ALL
  SELECT null,'C','20090101'