Update Table_name From Table

Update Table_name From Table_name As Alias

If a table is mentioned more than once in the ‘From’ clause of delete statement, and both instances have an alias name, in MS SQL Server you always have to refer to the alias. In ASE, you can use the table name in the ‘Delete’ clause

Example

create table Test (i int, j int)

insert into Test values (1, 10)

insert into Test values (2, 11)

insert into Test values (3, 12)

insert into Test values (4, 13)

-- This statement will not work on MS SQL Server

update Test set j = 100 from Test t1, Test t2 where t1.i = 2* t2.i

select * from Test order by i

drop table Test

Remarks

In MS SQL Server, use the following statement instead.

update t1 set j = 100 from Test t1, Test t2 where t1.i = 2* t2.i

Because the table ‘Test’ only occurs once in the statement below, this statement works both on ASE and MS SQL Server.

update Test set j = 100 from Test t1 where t1.i in (2, 3)