Lesson 3	Overview of updating data
Objective	Describe how to update data with Transact-SQL.

Updating Data in SQL Server

To update data in a table, you use the UPDATE Transact-SQL statement, specifying the names of the specific columns you want to change. The UPDATE statement uses the following general syntax:


UPDATE table_name
SET column_name1 = value1, column_name2 = value2, ...
WHERE condition;

table_name is the table containing the data you want to update.
column_name1, column_name2, ... are the columns you want to update.
value1, value2, ... are the new values for those columns.
condition identifies which rows should be updated; only rows that satisfy it are changed.

For example, suppose you have a table named Employees with columns EmployeeID, FirstName, LastName, and Salary. To update the salary of the employee with EmployeeID 101:


UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;

Update existing rows with Transact-SQL, SQL Server 2025, 1 of 3. UPDATE dbo.Product SET Price = Price + 5.00 WHERE ProductID = 101; with UPDATE labeled as the target table, SET labeled as the new value, and WHERE labeled as the matching rows. A before and after table shows ProductID 101, Keyboard, going from Price 75.00 to 80.00, while ProductID 102, Mouse, stays at 25.00. Notes state that without WHERE, every row in the target is eligible, and that SET can use expressions, DEFAULT, or NULL where allowed. — The basic UPDATE statement: target table, SET, and WHERE.

Without a WHERE clause, every row in the target table is eligible for the update, so always include a precise condition unless you genuinely intend to change every row. SET can also use expressions rather than only literal values, which is how you increment or adjust an existing value in place:


UPDATE table_name
SET column_name = column_name + 1
WHERE condition;

Updating From Another Table

UPDATE can pull its new values from another table, combined with FROM and a JOIN:


UPDATE  p
SET     Price = s.NewPrice
OUTPUT  inserted.ProductID,
        deleted.Price  AS OldPrice,
        inserted.Price AS NewPrice
FROM    dbo.Product     AS p
JOIN    dbo.PriceChange AS s
        ON s.ProductID = p.ProductID
WHERE   s.Approved = 1;

p is the target table's alias, and the join against dbo.PriceChange supplies the new price for each matching row. The OUTPUT clause here returns both the old and new values in one step: deleted represents each row's values before the update, and inserted represents its values after.

Update from a source and return changes, SQL Server 2025, 2 of 3. UPDATE p SET Price = s.NewPrice OUTPUT inserted.ProductID, deleted.Price AS OldPrice, inserted.Price AS NewPrice FROM dbo.Product AS p JOIN dbo.PriceChange AS s ON s.ProductID = p.ProductID WHERE s.Approved = 1; with p labeled as the target alias, the JOIN labeled as supplying the new price, and a note that deleted equals before and inserted equals after. A flow shows dbo.PriceChange with ProductID 101, NewPrice 80.00, Approved 1, feeding into dbo.Product before the update at ProductID 101, Price 75.00, producing an OUTPUT result of ProductID 101, OldPrice 75.00, NewPrice 80.00. Notes state to require at most one source match per target row, and that OUTPUT values precede AFTER triggers, so results should only be used after success. — Updating from another table with a JOIN, and capturing old and new values with OUTPUT.

This pattern depends on the join matching at most one source row per target row. If the join is one-to-many, more than one source row matching a single target row, SQL Server does not guarantee which matching row's value is the one actually applied, so a one-to-many join condition here is a bug waiting to surface, not just a style issue. It's also worth knowing that OUTPUT values are produced before any AFTER triggers run; if an AFTER trigger later causes the statement or transaction to fail, the client may already have received OUTPUT results for a change that didn't ultimately persist. Treat OUTPUT results as provisional until you know the statement actually succeeded.

Specialized UPDATE Options

A few more capabilities worth knowing, even if you won't reach for them often.

Other update targets. You can update through an updatable view, or through OPENQUERY/OPENROWSET against a remote data source, provided the underlying provider supports remote updates.
Table and query hints. A table hint such as WITH (ROWLOCK) follows the target table; a query hint such as OPTION (RECOMPILE) goes at the very end of the statement. Both override SQL Server's own choices for locking or plan compilation, so use them only after you've actually measured a problem, not as a default habit.
Cursor-positioned updates. WHERE CURRENT OF cursor_name updates the single row the cursor is currently positioned on, and requires an open, fetched, updatable cursor.
Capturing a value in a variable, for a single row. Within a single-row update, you can capture a column's old or new value into a local variable as part of the same statement. Comma-separated assignments read the pre-update value:
```
SET @OldPrice = Price,
    Price = Price + 5.00
```
while a chained assignment captures the post-update value instead:
```
SET @NewPrice = Price = Price + 5.00
```
Both of these only make sense when the WHERE clause restricts the statement to a single row; a local variable can only hold one value, so if multiple rows are affected, which row's value ends up in the variable is undefined. For multiple rows, use OUTPUT instead, as shown above, to return old and new values for every row that changed.

Optimized Locking

SQL Server 2025 introduces Optimized Locking, a database-level feature that can reduce the memory used by locks and cut down on blocking during INSERT, UPDATE, DELETE, and MERGE statements. It does not change UPDATE syntax at all, only how SQL Server manages locks internally while the statement runs.

Optimized Locking is off by default, and turning it on isn't a single switch: Accelerated Database Recovery (ADR) must already be enabled on the database before Optimized Locking can be turned on at all, and enabling Read Committed Snapshot Isolation (RCSI) alongside it is what unlocks its largest concurrency benefit. Without RCSI, only part of the feature is actually doing anything. If you're troubleshooting blocking on a SQL Server 2025 instance, it's worth checking whether ADR, RCSI, and Optimized Locking are actually all turned on together, rather than assuming one implies the others.

The IDENTITY Column

Unlike an INSERT statement, which can supply explicit values for an IDENTITY column once IDENTITY_INSERT is turned on, an UPDATE statement cannot change the value of an IDENTITY column at all; there is no equivalent override.

In the next lesson, you will learn about the DELETE statement.