Sql-server – Update query could not be bound

sql serverupdate

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)

Best Answer

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;

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.

Related Solutions

Sql-server – Statement could not be prepared

There are a couple JOIN combinations that seem abnormal to me.

For example:

left outer join dbo.vwQuoteStatus 
     inner join appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

vs.

left outer join dbo.vwQuoteStatus 
                appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

Sql-server – Query intermittently fails with “The multi-part identifier could not be bound”

If it's just one row, how about:

UPDATE LocalServer.DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
) 
WHERE LocationID = '1';

Now you've eliminated the join and any aliases that have to be bound. Also why are you referencing both with 4-part names? If the local server is really the same instance, why not reference the local as DB.dbo.table instead of LocalServer.DB.dbo.table? If you take out the four-part name on the outer table, you can also do this for multi-row updates. It's not as pretty to do the aliasing this way, though:

UPDATE DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
  WHERE key_col = DB.dbo.table.key_col
) 
WHERE LocationID = '1';

And as we discussed offline, if it's multiple rows and multiple columns, you should use something like this:

UPDATE t
SET [ChangeStatus] = r.[ChangeStatus],
    [OtherColumn]  = r.[OtherColumn]
FROM DB.dbo.table AS t
INNER JOIN RemoteServer.DB.dbo.table AS r
  ON t.key_col = r.key_col
WHERE t.LocationID = '1';

I believe that proper aliasing and eliminating the 4-part name should cause less work for the relevant parts of the execution process.

Best Answer

Related Solutions

Sql-server – Statement could not be prepared

Sql-server – Query intermittently fails with “The multi-part identifier could not be bound”

Related Question