-1
UPDATE [cart]
SET    [cart].item_id = [linked_server].[database].[dbo].[linked_cart].item_id
FROM   [linked_server].[database].[dbo].[linked_cart]
INNER JOIN [products] ON [cart].product_id = [products].product_id 
WHERE  [linked_server].[database].[dbo].[linked_cart].sales_id = 'ABC'
  AND  [linked_server].[database].[dbo].[linked_cart].product_name = [products].product_name 
  AND  [linked_server].[database].[dbo].[linked_cart].line_id = [cart].line_id

It's complaining when I give the full path to the linked server, and the table Cart in the INNER JOIN line. The error is

The multi-part identifier [cart].product_id could not be bound.

It also does this for the linked server tables.

Is there a workaround? The error does go away if I remove the identifiers, but then it changes ALL rows in Cart because of ambiguity I'm guessing.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jan Trindal
  • 219
  • 2
  • 9
  • 1
    Do you realize that you don't have a table named (or aliased) "Cart" in your FROM clause? The error message was a big hint. If you want to update a table named "Cart", and you need to use a FROM clause to join other tables, you must include "Cart" in the FROM clause. https://stackoverflow.com/questions/982919/sql-update-query-using-joins – Tab Alleman Jul 10 '19 at 17:07
  • @TabAlleman Thanks, but it's also happening for `[linked_server].[database].[dbo].[linked_cart]` in the WHERE clause, and that table is included in the FROM clause. Is that supposed to be in a JOIN then? – Jan Trindal Jul 10 '19 at 17:16

1 Answers1

1

If you need to update a table using join, you must include it to FROM as well.

UPDATE [cart]
SET    [cart].item_id = lc.item_id
FROM   [cart]
INNER JOIN [products] ON [cart].product_id = [products].product_id 
INNER JOIN [linked_server].[database].[dbo].[linked_cart] lc ON 
         lc.sales_id = 'ABC'
         AND lc.product_name = [products].product_name 
         AND lc.line_id = [cart].line_id
Serg
  • 22,285
  • 5
  • 21
  • 48