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:
DELETE FROM [Order Details]
FROM [Order Details] AS OD JOIN Orders AS O
ON OD.orderid = O.orderid
WHERE customerid = 'VINET'
Note that this statement contains two FROM clauses, which might seem strange. The first FROM clause is optional, so you might prefer not to use it, but a DELETE query that doesn't perform a join is more readable with a FROM clause. Also, the Order Details table appears twice. The first occurrence (after the first FROM clause) specifies which table the statement modifies, and the second occurrence (after the second FROM clause) is used for the JOIN operation. This syntax doesn't let you specify more than one table after the first FROM clause. If it did, you wouldn't be able to determine which table the statement modified. Now, write the code inside a transaction, and use a ROLLBACK statement so the change won't commit in your database:
BEGIN TRAN
-- check the rows you intend to delete
SELECT *
FROM [Order Details] AS OD JOIN Orders AS O
ON OD.orderid = O.orderid
WHERE customerid = 'VINET'
-- delete rows
DELETE FROM [Order Details]
FROM [Order Details] AS OD JOIN Orders AS O
ON OD.orderid = O.orderid
WHERE customerid = 'VINET'
-- check to see that all rows are deleted
SELECT *
FROM [Order Details] AS OD JOIN Orders AS O
ON OD.orderid = O.orderid
WHERE customerid = 'VINET'
ROLLBACK TRAN