Updating a T-SQL table from rows in the same table

I had to do a very simple Microsoft SQL query in which I wanted to update some of the values in a row from a row in the same table. Actually, the query was already there, but was using two local variables to store the information, then make the update. Something like this:

DECLARE @Var1 INT
DECLARE @Var2 INT
SELECT @Var1=Column1,@Var2=Column2 FROM MyTable WHERE ID=1
UPDATE MyTable SET Column1=@Var1,Column2=@Var2 WHERE ID=2

I really hated that I was using two SQL statements and all that declaring to do a simple update, so I looked up the syntax for the UPDATE statement. It said that if I want to update a table from a source I need to use the FROM keyword, like this:

UPDATE MyTable 
  SET Column1=Alias.Column1,Column2=Alias.Column2
FROM MyOtherTable AS Alias
WHERE ID=2
  AND Alias.ID=1

As you can see, we use an alias to name another table or query, we use the Alias name for all the conditions for that table and nothing for the conditions on the table we update. Easy, no? I even tested it and it worked. So I tried this:

UPDATE MyTable 
  SET Column1=Alias.Column1,Column2=Alias.Column2
FROM MyTable AS Alias
WHERE ID=2
  AND Alias.ID=1

I used the same table to update and to alias and it seemed to work. However, the number of updated columns was always 0. Remarkable how difficult it is to find on the net a straight answer about a simple situation like this.

It turns out that even with the alias, MSSql is confusing some things. The solution is to use a query from your table, rather than the name of the table itself. Here is how you do it:

UPDATE MyTable 
  SET Column1=Alias.Column1,Column2=Alias.Column2
FROM (SELECT * FROM MyTable) AS Alias
WHERE ID=2
  AND Alias.ID=1

SQL 2005 also introduced Common Table Expressions, which can be used to clarify a query. In this case, using a CTE results in the same execution plan and makes the entire query even more convoluted:

WITH Alias(Column1,Column2)
AS (
  SELECT Column1, Column2 FROM MyTable
)
UPDATE MyTable 
  SET Column1=Alias.Column1,Column2=Alias.Column2
FROM Alias
WHERE ID=2
  AND Alias.ID=1

Even if the documentation says you can specify a CTE without declaring the column names, I couldn't do it in this situation, I don't know why. I admit I only tried the CTE solution for a minute before discarding it as too verbose.