Supplierlist.id & supplierlist.tem columns - what are they?

Hi Jerry, The supplierlist.id column is auto number generated column it is a sequence no for each row in supperlist, you can also called it primary key as basically it generate the Unique ID.

The supplierlist.Itemid column is link column (forigen key) which make relationship with your item table this can be repeated and you make relationship with item table via supplierlist.itemid=item.id

Same as ItemID you have supplierID column also it has also l> I was able to make the update and the item table looks correct but > the

The last part of your where clause should have been: and supplierlist.supplierID = 20

What you have done is updated EVERY Supplier (Yes an Item can have multiple Suppliers) for every item that has Supplier 20 as the 'primary supplier'.

What you wanted was to Update ONLY Supplier 20 for every item that they supply.

By the way, I HATE these implicit joins - too easy to join by things that you didn't mean to join by.

Glenn Adams Tiber Creek C> I was able to make the update and the item table looks correct but > the

Thanks Glenn - I ran it on a test database first anyway so no harm. I appreciate the catch.

Is it an implicit join if the column names are fully qualified with the table name? I guess I don't know what you mean by an implicit join here?

from dbo.Sheet1$,Item where dbo.Sheet1$.UPC=Item.ItemLookupcode and item.id=supplierlist.itemid and supplierList.supplierid

From Sheet1$ s inner join Item i on s.UPC = i.ItemLookupcode inner join SupplierList sl on sl.ItemID = i.ID AND sl.SupplierID = 20

You get the same result from either syntax; I just find the explicit version much clearer about which fields are being used to link the tables.

Glenn Adams Tiber Creek C> >> The last part of your where clause should have been:

I'm finding that I have multiple supplierlist.id for the same item.itemlookup. Supplierid all = 20 so it's the same supplier. Not sure how come I have 4 supplierlist.id in many cases with the same supplierid:

select item.itemlookupcode, item.description, supplierlist.reordernumber, supplierlist.id, item.lastupdated, supplierlist.itemid from supplierlist, item where supplierlist.supplierid and item.id=supplierlist.itemid order by 1

The only unique value is the supplierlist.id. Itemlookupcode, itemid, lastupdated and reorder number are unique.