I thought this was a good question because I've run across a number of people who didn't realize it was possible, but the example troubles me. It just won't work. I almost answered no, thinking it was a trick question. The example really needs to be cleaned up.

update table

set a1.coulmn=a2.column<

from table1 as a1, table2 as a2

where a1.colum1 = a2.column2

First, just saying UPDATE TABLE will throw an error. It should say UPDATE

, unless it is a typo. I am assuming it's a typo, just like the typos in spelling "column" and the extra < at the end of one line. If not, it reads as if there were three tables: "table", "table1" and "table2"... which won't work either.

We are supposed to be updating the table specified in the first line, but instead the code tries to set a value of a column that was aliased in the FROM (TABLE1 as A1). I've never seen that format so I cleaned it up at little and tested it. It throws the following error:

The only columns which can be updated are those belonging to the table which is the subject of the UPDATE. That table's name is specified immediately after the word UPDATE, and not necessarily after the FROM. Since only one target table can be the target, the names of the target columns do not need to be qualified, although the source column should be.

Two correct formats for using FROM with an UPDATE are found in the examples below.

;with cte (id, val) as (

select 1,'A' union all

select 2,'B' union all

select 3,'C'

)

select *

into [table1]

from cte

;with cte (id, val) as (

select 1,'X' union all

select 2,'Y' union all

select 3,'Z'

)

select *

into [table2]

from cte

select 'Table1',* from [table1]

select 'Table2',* from [table2]

select 'NOW THE UPDATE HAPPENS' as [GASP!!!]

------------------------------------------------------------------

----------- proper format for using a FROM clause in an update

------------------------------------------------------------------

update table1

set val=a2.val

from table1 a1

join table2 a2 on a1.id = a2.id

----------- (yes, i used a join instead of tablelist/where)

------------------------------------------------------------------

select 'Table1',* from [table1]

drop table table1

drop table table2

Alternatively this will work as well

update table1

set val=a2.val

from table2 a2

where table1.id = a2.id