UPDATE (Level 4)

Installing SQL Server

previous page next page
Installing SQL Server
UPDATE (Level 4)
SQL Server 6.x SQL Server 2000
In Microsoft® SQL Server™ version 6.0, the following UPDATE statement, using two different table aliases for the same base table, was allowed: 
CREATE TABLE t1 (c1 int)
GO
INSERT t1 VALUES (1)
INSERT t1 VALUES (2)
GO
UPDATE t1 
SET c1 = 50
FROM t1 a1, t1 a2
WHERE a1.c1 = 1 AND
a2.c1 = 2
GO
Syntax no longer supported. Use the alias, rather than the table name, after the UPDATE keyword. The UPDATE statement would be rewritten to: 
UPDATE a1 
SET c1 = 50
FROM t1 a1, t1 a2
WHERE a1.c1 = 1 AND
a2.c1 = 2

Expect differences in behavior as compared to SQL Server version 6.0.
This UPDATE statement with table and alias references worked. 
USE pubs
GO
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = 
(SELECT MAX(sales.ord_date) FROM sales)
GO
The alias specified after the UPDATE keyword must match the alias specified following the SET keyword. Without this change, the compatibility level setting must be changed to 65 for this UPDATE statement to function as it did in version 6.x.

Here is the same UPDATE statement rewritten:

USE pubs
GO
UPDATE t 
    SET t.ytd_sales = t.ytd_sales + s.qty
        FROM titles t, sales s
           WHERE t.title_id = s.title_id
            AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
GO

Expect differences in behavior as compared to SQL Server version 6.x when using different table references following the UPDATE keyword of the UPDATE statement and the SET keyword of the UPDATE statement.
previous page start next page