How to modify table records with JOIN syntax in SQL Server?

Data and Databases
1

I’m trying to modify records in one table by joining it with another table but I keep getting syntax errors. Here’s what I’m attempting:

UPDATE customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
SET c.total_amount = o.order_total
WHERE 
    o.customer_id = c.customer_id
AND c.region = 'North'

I’m getting this error message:

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ‘c’.

Can someone help me understand what’s causing this syntax issue? I thought this would be the correct way to update records using a join condition in SQL Server.

2

oh interesting! I’ve seen this trip up tons of people switching from mysql or postgres. sql server’s really picky about syntax. @growingtree what database were you using before? curious if you hit other weird sql server quirks during the switch.

3

yea, this tripped me up too when learning sql server! using the FROM clause works, but if you need complex logic, use MERGE. just make sure ur join condition is tight - i’ve updated way more rows than i intended cause of sloppy joins.

4

SQL Server’s UPDATE syntax with JOINs is different from other databases. You can’t put table aliases right after UPDATE - you need the full table name there, then use aliases in the JOIN and WHERE clauses. Here’s how to fix it: sql UPDATE customers SET customers.total_amount = o.order_total FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE c.region = 'North' I also removed that redundant o.customer_id = c.customer_id condition from the WHERE clause since the JOIN already handles it. That’s just how SQL Server wants it done.