none
updating column data with lookup from another

    Question

  • I need help i am new to this

    UPDATE products
    
    SET vendor_id = dbo.vendors.id
    FROM dbo.products t2
    INNER JOIN dbo.vendors t1
    ON t2.vendor_code = t1.vendor_code
    
    
    GO

    I am trying to update the products.vendor_id column with the correct vendor_id value based on a matching vendor_code in each table. I get the following error.

    The multi-part identifier "dbo.vendors.id" could not be bound.

    Monday, July 28, 2014 12:09 AM

Answers

  • I had my joins the wrong way around i think i updated my query with the following code which works.

    UPDATE dbo.products
    SET vendor_id = dbo.vendors.id
    FROM dbo.vendors
    WHERE dbo.products.vendor_code = dbo.vendors.vendor_code
    
    GO

    • Marked as answer by Drifyt Monday, July 28, 2014 12:28 AM
    Monday, July 28, 2014 12:28 AM

All replies

  • I had my joins the wrong way around i think i updated my query with the following code which works.

    UPDATE dbo.products
    SET vendor_id = dbo.vendors.id
    FROM dbo.vendors
    WHERE dbo.products.vendor_code = dbo.vendors.vendor_code
    
    GO

    • Marked as answer by Drifyt Monday, July 28, 2014 12:28 AM
    Monday, July 28, 2014 12:28 AM
  • merge dbo.products tgt
    using dbo.vendors src on tgt.vendor_code = src.vendor_code
    When matched then
    UPDATE 
    SET vendor_id = src.id;

    Monday, July 28, 2014 4:22 AM
  • UPDATE products
    SET vendor_id = dbo.vendors.id
    FROM dbo.products t2
    INNER JOIN dbo.vendors t1
    ON t2.vendor_code = t1.vendor_code

    The error here is simply that once you have introduced an alias for a table, you cannot use the table name itself as a prefix, but you need to use the alias throughout the query:

     UPDATE products
     SET vendor_id = t1.id
     FROM dbo.products t2
     INNER JOIN dbo.vendors t1
     ON t2.vendor_code = t1.vendor_code

    Or better:

     UPDATE products
     SET vendor_id = v.id
     FROM dbo.products p
     INNER JOIN dbo.vendors v
     ON p.vendor_code = v.vendor_code

    That is, use aliases that are related to the tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Avijit Swain Monday, July 28, 2014 9:57 AM
    Monday, July 28, 2014 8:01 AM