Suppose that the Northwind database tracks orders by store, and the orderid column alone isn't unique. The primary key of the Orders table is expanded to orderid, storeid; and the primary key of the Order Details table is expanded to orderid, storeid, productid. Now, if you want to delete all rows from the Order Details table for orders that the customer VINET places, you need to perform a composite key join:
DELETE FROM [Order Details]
FROM [Order Details] AS OD JOIN Orders AS O
ON OD.orderid = O.orderid
AND OD.storeid = O.storeid
WHERE customerid = 'VINET'
You can't use the IN operator to implement this DELETE statement, but you can use the EXISTS operator to implement it:
DELETE FROM [Order Details]
WHERE EXISTS(SELECT customerid
FROM Orders
WHERE [order details].orderid = Orders.orderid
AND [order details].storeid = Orders.storeid
AND customerid = 'VINET')
The UPDATE statement contains a similar syntax:
UPDATE <modified_table>
SET col1 = <new_value>[, col2 = <new_value>...]
[FROM <modified_table> <join_type>
JOIN <another_table>
ON <join_condition>]
[WHERE <search_condition>]
Suppose you want to add a 5 percent discount to order items in the Order Details table in which the supplier Exotic Liquids, whose supplierid is 1, supplies the parts. The problem is that the supplierid column appears in the Products table. Write this UPDATE statement:
BEGIN TRAN
SELECT...
UPDATE OD
SET discount = discount + 0.05
FROM [Order Details] AS OD JOIN Products AS P
ON OD.productid = P.productid
WHERE supplierid = 1
SELECT...
ROLLBACK TRAN
Note that you can use either the table name or the table qualifier to specify the table you're modifying. These alternatives use subqueries:
BEGIN TRAN
SELECT...
UPDATE [Order Details]
SET discount = discount + 0.05
WHERE productid IN (SELECT productid
FROM Products
WHERE supplierid = 1)
SELECT...
ROLLBACK TRAN
BEGIN TRAN
SELECT...
UPDATE [Order Details]
SET discount = discount + 0.05
WHERE EXISTS(SELECT supplierid
FROM Products
WHERE [Order Details].productid = Products.productid
AND supplierid = 1)
SELECT...
ROLLBACK TRAN
You can use joins and subqueries to modify a table based on data in another table. I demonstrated INNER joins, but you can also use OUTER joins. The optimizer tends to produce efficient plans for joins, but in many cases, the optimizer produces the same plan for the subquery equivalent. For example, when you use the EXISTS() function, the optimizer might come up with a better plan than the join query equivalent. To check the number of logical reads, SET STATISTICS IO ON, and to examine the execution plan, use SET SHOWPLAN_TEXT ON or the Graphical Execution Plan in the Query Analyzer. Try this with the first three variations of the DELETE statement that I've presented in this article, and you'll get the same plan for the JOIN query and the two subqueries. Whatever method you choose, you need to test, test, and test.