Update query could not be bound

Question

I'm trying to do a simple update query and I'm getting this error:

The multi part identifier "customers.customer_name" could not be bound

Query:

Update Suppliers
Set City = (select s.city
 From suppliers AS s
 Where s.supplier_name = customers.customer_name)
Where Exists (select c.city
 From customers AS c
 Where c.customers_name = Suppliers.supplers_name)

Answer 1

Your syntax is slightly off. In SQL Server, here is how I would perform this update:

UPDATE s SET s.city = c.city
FROM dbo.Suppliers AS s
INNER JOIN dbo.Customers AS c
ON s.supplier_name = c.customer_name;

From my most popular answer on Stack Overflow.

Remember to always use the schema prefix and also it is often easier to just call those columns name instead of prefixing each with the table name.