We discuss some best practices, limitations, and wrap-up with several examples. You can start at the beginning by reading Introduction to SQL Server Data Modification Statements.All the examples for this lesson are based on Microsoft SQL Server Management Studio and the Adventure Works2012 database.Though an update statement can modify columns data from many sources, such as literal values or other query results, the basic format is the same.There are three components to an UPDATE statement: We’re now going to do some sample updates, so if you haven’t done so already, run the script to create the esql Sales Person table.In this example we insert a single row into the esql Sales Person table.Here is its table structure: Let’s assume we want to the city for every sales person to Ann Arbor.
No rows from the UPDATE statement are saved into the table, even those rows that didn’t generate an error.
You can run the following to do so and then verify all cities are NULL. You can easily adjust the values using the following statement: However, suppose the sales department want a record of all changes.
Once you look at the statement you’ll see we added FROM and INNER JOIN clauses. Sure, you could run a query before the update, one after the update, and then compare, but rather than doing all of that we can use the OUTPUT clause to log the changes directly into a table.
An exclusive (X) lock is always placed on a table when an update is run.
The lock is held in place until the update statement completes.
The UPDATE statement is capable of updating more than one row. All rows returned via the WHERE clause criteria are updated.