Problems with MSSQL Update using Joins

[computertim]

Hi all,
I have a spreadsheet with UPC codes and vendor product numbers, and I'm trying to run a script to update my database with that information. The UPC code is in the Item table while the Vendor product # is in the SupplierList table...thought I would be able to do a Join to link these and update the information. Maybe I thought it was too simple...?

update supplierlist left join supplierlist on supplierlist.itemid=item.id set supplier.reordernumber='1234' where item.itemlookupcode='test'

[r937]

you had two errors

first, you joined supplierlist to itself instead of to item

second, you used mysql syntax -- mssql syntax is different

UPDATE supplierlist
   SET reordernumber = '1234' 
  FROM supplierlist t1
INNER
  JOIN item t2
    ON t2.id  = t1.itemid
 WHERE t2.itemlookupcode = 'test'

[computertim]

you had two errors

first, you joined supplierlist to itself instead of to item

second, you used mysql syntax -- mssql syntax is different

UPDATE supplierlist
   SET reordernumber = '1234' 
  FROM supplierlist t1
INNER
  JOIN item t2
    ON t2.id  = t1.itemid
 WHERE t2.itemlookupcode = 'test'

Thanks much!