How to UPDATE with JOIN in SQL Server (With Examples)

Introduction

SQL Server uses a FROM clause for UPDATE JOINs — different from MySQL. This T-SQL syntax is powerful and supports features like OUTPUT and CTE-based updates.

UPDATE with FROM JOIN

SQL Server puts the JOIN in a FROM clause after SET — not between UPDATE and SET like MySQL.

-- Update product prices from a price list:
UPDATE p
SET p.price = pu.new_price,
    p.updated_at = GETDATE()
FROM products p
INNER JOIN price_updates pu ON p.sku = pu.sku;

UPDATE with OUTPUT (See What Changed)

OUTPUT clause shows you exactly which rows were modified — like PostgreSQL's RETURNING.

-- Update and see the changes:
UPDATE p
SET p.price = pu.new_price
OUTPUT
  inserted.sku,
  deleted.price AS old_price,
  inserted.price AS new_price
FROM products p
INNER JOIN price_updates pu ON p.sku = pu.sku;

UPDATE with CTE

Use a CTE (Common Table Expression) for complex update logic — cleaner than nested subqueries.

-- Update using a CTE:
WITH customer_stats AS (
  SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
UPDATE c
SET c.total_orders = cs.order_count,
    c.lifetime_value = cs.total_spent
FROM customers c
INNER JOIN customer_stats cs ON c.id = cs.customer_id;

UPDATE TOP N Rows

Limit how many rows are updated — useful for batch processing.

-- Update only the first 1000 rows:
UPDATE TOP (1000) o
SET o.status = 'processed'
FROM orders o
WHERE o.status = 'pending'
AND o.created_at < DATEADD(day, -30, GETDATE());

UPDATE with Subquery

Use a correlated subquery when you need to update based on aggregated data from the same table.

-- Set rank based on revenue:
UPDATE p
SET p.rank_in_category = sub.rnk
FROM products p
INNER JOIN (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS rnk
  FROM products
) sub ON p.id = sub.id;

Best Practices

• Always use aliases (UPDATE p FROM products p) for clarity
• Use OUTPUT clause for audit trails
• Use CTE for complex update logic instead of nested subqueries
• Batch large updates with TOP to avoid lock escalation
• Test with SELECT first: replace UPDATE SET with SELECT to preview

Generate SQL Server Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL Server query instantly.

Frequently Asked Questions