How to write UPDATE SQL with Table alias in SQL Server 2008?

Why use an alias in the UPDATE statement?

In SQL Server, the UPDATE statement does not allow an alias immediately after the keyword. Aliases must be defined within the FROM clause after the SET statement or within a CTE for complex queries.

Benefit of using aliases

Aliases in an UPDATE operation may seem unnecessary or purely academic, but they are absolutely essential in circumstances involving complex queries or updating based on a table subset. In situations where multiple tables are involved or subqueries are used, aliases offer clarity.

Common mistakes to avoid

A common error is trying to use an alias right after the UPDATE keyword. That's a big no-no! (And results in an error). Instead, follow the patterns shown in the Fast answer section. Using a CTE in such a scenario is particularly helpful to maintain readability—kinda like putting on glasses for better vision!

Harness the CTE power

Common Table Expressions (CTE) are a superhero in disguise for complex scenarios. CTEs encapsulate a temporary result set which can be updated like a regular table. This not only heightens readability but also maintainability, especially for multi-step update operations.

Mastering Advanced alias usage

Combine JOIN with UPDATE alias

SQL syntax isn't as scary as a spider, I promise! You can even combine an alias with JOIN operations in updates. Let me show you how:

-- Avengers, assemble!
UPDATE t
SET t.Name = p.Name
FROM Transactions AS t
INNER JOIN Payments AS p ON t.TransactionID = p.TransactionID
WHERE p.Status = 'Completed'


This uses a join to update one table based on conditions in another, utilising aliases for the sake of clarity.

Joined tables and aliasing

When working with joined tables, you need to give each one a distinct alias to distinguish them:

-- It's like a secret handshake among tables
UPDATE t1
SET t1.Price = t2.DiscountedPrice
FROM Products AS t1
JOIN (SELECT ProductID, DiscountedPrice FROM Discounts) AS t2
ON t1.ProductID = t2.ProductID


This pattern uses a subquery alias to facilitate the update from the Discounts table.

Capture the changes with OUTPUT

After performing an update, you may want to capture the changes. As useful as a cape on a superhero, SQL Server's OUTPUT clause allows this:

-- Like sneaking a peek at your presents
UPDATE Products
SET Price = Price * 0.9
OUTPUT INSERTED.ProductID, INSERTED.Price, DELETED.Price
WHERE CategoryID = 2


Using OUTPUT, you can easily validate updates and audit changes. In short, OUTPUT is the Sherlock Holmes for monitoring your updates!