SQL to set a supplier

Craig,

are those items currently assigned to any supplier? or they don't have any supplier assigned?

Hi Ahmed, thanks for the quick reply. Some have a supplier assigned(wrong one)and some have no supplier. Craig

Craig,

execute the following statements, note that the supplier cost will be copied from the item cost so change it if u want, plz backup ur db first

update item set item.supplierid=(select id from supplier where code='my code') from department where department.id=item.departmentid and department.name='ff'

INSERT INTO SupplierList SELECT DISTINCT '1' as MinimumOrder,Item.ID as ItemID,Item.SupplierID as SupplierID,Item.Cost as Cost,'0' as ReorderNumber ,'1' as MasterPackQuantity,NULL as DBTimeStamp,'0' as TaxRate FROM Item,department where department.id=item.departmentid and department.name='ff'

Ahmed, Worked perfectly! Thanks for your help. Two more questions if I may, if I wanted to add another supplier to the department can I use the same statement without effecting the supplier that's already there. And is there a statement to remove a supplier from the department(the wrong supplier that was already there)? Again, thanks for your help, I really appreciate it. Craig

Craig,

to add another supplier, use only the following statement: -

INSERT INTO SupplierList SELECT DISTINCT '1' as MinimumOrder,Item.ID as ItemID,(select id from supplier where code='sdsd') as SupplierID,Item.Cost as Cost,'0' as ReorderNumber ,'1' as MasterPackQuantity,NULL as DBTimeStamp,'0' as TaxRate FROM Item,department where department.id=item.departmentid and department.name='ff'

to remove the unused supplier assuming that it's not the primary one: -

delete supplierlist where supplierid=(select id from supplier where code='dfdf') and itemid in (select item.id from item, department where department.id=item.departmentid and department.name='ff')

Thanks Ahmed. Craig