Help with an SQL statement please!

I could really use some help with this one! I need an SQL statement that will insert a second supplier into the item properties of over 16,000 items- the entire contents of the Item table. This second supplier is to be the primary supplier with a reorder number thats the same as the item lookup code and a cost price of zero. I'm not too sure how to proceed as I think we're talking the SupplierList table and the Item table. Any advice would be appreciated! The exact statement would win the provider my eternal gratitude!

Reply to
Tara
Loading thread data ...

INSERT INTO SupplierList ( SupplierID, ItemID, ReorderNumber) SELECT Supplier.ID, Item.ID, Item.ItemLookupCode FROM Item, Supplier WHERE Supplier.Code = ''

cod

Reply to
cod

HI Tara,

Following statement would do this, however you can also change it per your need. Let me know if it helps you:

insert into supplierlist (ItemId,SupplierID,Cost,ReorderNumber) select item.ID,Supplier.ID,Item.cost,item.itemlookupcode As reordernumber from item,supplier where supplier.code='DATUM'

Change the Supplier.Code to your supplier code this is just to check one supplier only you can take out to insert all the supplier x all item.

"Tara" wrote:

Reply to
Akber Alwani

Reply to
Tara

ALWAYS take a backup of your database before running ANY Update, Insert or Delete statements!

No, it won't make the new supplier the Primary. To do that, you would need:

UPDATE Item SET SupplierID = X

Replace 'X' above with the results of this query: select ID from Supplier where Code = ''

ALWAYS take a backup of your database before running ANY Update, Insert or Delete statements!

Do> Thank you- I'll try those. Will that insert the second supplier into the

Reply to
Glenn Adams

You cannot have two Supplier entries for an item that point to the same supplier. If you try to add a duplicate Supplier to an item, RMS won't let you. Performing this operation with a query will probably cause problems.

Tom

Reply to
Terrible Tom

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.