SQL Server中使用DELETE INNER或UPDATE INNER

在 SQL Server 中使用 DELETE 和 UPDATE 的 INNER JOIN 关键字与 Access 的常规写法不同。

Access中写为：

delete from t1 inner join t2 on t1.id = t2.tid

而SQL Server中须写为：

delete from t1 from t1 inner join t2 on t1.id = t2.tid

注意蓝线部分！

同样，Update的写法也有所有不同。

Access中：

update t1 inner join t2 on t1.id = t2.tid set t1.name='Liu'

SQL Server中：

update t1 set t1.name='Liu' from t1 inner join t2 on t1.id = t2.tid

参照文档：

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8808

Editor's Note: This is the debut of T-SQL Black Belt, a series of articles that demonstrate practical, advanced tips for using T-SQL. Send your experts-only T-SQL tips to SQL Server MVP Itzik Ben-Gan at blackbelt@sqlmag.com. If we use your submission, you'll receive $100 and an exclusive T-SQL Black Belt shirt.

Sometimes you need to modify data, but the criteria that define which rows will be affected are based on data that doesn't exist in the modified table but in another table. You could use subqueries to solve the problem; or you could use a syntax that originated from Sybase and uses joins in the DELETE and UPDATE statements. This syntax isn't ANSI-compliant and might look strange at first glance. But if you're comfortable writing join statements, you'll find the syntax convenient, especially for inside triggers, in which you usually need to join the base table to the inserted or deleted tables.

These examples use Northwind sample database tables. Let's start with an abbreviated form of the DELETE statement syntax:

DELETE [FROM] <modified_table>

[FROM <modified_table> <join_type> 

JOIN <another_table>

ON <join_condition>]

[WHERE <search_condition>]

Suppose you want to delete from the Order Details table all rows for orders that the customer VINET places. The problem is that the Order Details table doesn't have information about the customer who made the order; this information is in the Orders table. The following DELETE statement will delete the appropriate rows: . . .