Inside Microsoft® SQL Server® 2008: T-SQL Querying

Updating Data

This section covers several aspects of updating data, including UPDATEs using joins, updating large values types, and SELECT and UPDATE statements that perform assignments to variables.

UPDATE Using Joins

Earlier in this chapter, I mentioned that T-SQL supports a nonstandard syntax for modifying data based on a join, and I showed DELETE examples. Here I’ll cover UPDATEs based on joins, focusing on cases where the nonstandard syntax has advantages over the supported standard syntax using subqueries. I’ll also show how you can use CTEs to update data based on joins.

I’ll start with one of the cases where an UPDATE based on a join has a performance advantages over an UPDATE using subqueries. Suppose you wanted to update the shipping information ...