SQL to enter Item.SupplierID for all child items

Hi Jason - I must admit this one stumped me but I think I figured it out - here's my disclaimer - I am not SQL certified but I've been reading up on it for the last 9 months or so that I've been involved with RMS....so my first suggestion is to wait a day or two to see if a more experienced SQL user can post a better solution or better approach (ie akber, jeff, @ cts, etc - actually, I went back to a post by relentless on how to update reorder/restock points based on sales history using a temporary variable and it got me started but I'm a little more polished with views so I took that route - I did this as a two-stepper - first I created a view that showed only references in supplierlist that had a reference as a parentitem in the item table; then I ran an insert statement using that view - here's what I did

create view vparentitemsuppliers as select itemid, supplierlist.supplierid from supplierlist inner join item on supplierlist.itemid = item.parentitem

the above created my view showing "parent items" and their corresponding supplier IDs (run select * from vparentitemsuppliers to see this)

then I used the following insert statement to populate supplierlist with child items insert into supplierlist (itemid, supplierid) select item.id, vparentitemsuppliers.supplierid from item right join vparentitemsuppliers on vparentitemsuppliers.itemid = item.parentitem

to test this I had created four items, two of which parent items to the other two - the child items had no supplier assigned - when running the insert statement, the child items were assigned their parent items supplier - BUT IT DIDN'T ASSIGN THE SUPPLIERID IN THE ITEM TABLE - so I then ran a third query

update item set supplierid = supplierlist.supplierid from item inner join supplierlist on item.id = supplierlist.itemid where item.supplierid = 0

this assigned a supplierid to the child items in the item table (I'm unsure as to the impact of not referencing a supplierid on the item table but having the item referenced in the supplierlist table - maybe affects a sales by supplier report???)

I don't know any potential impacts of dormant views, but if you need to get rid of the view run .... drop view vparentitemsuppliers

I have the luxury of playing around on a demo database on my laptop - but you probably have to work an an actual production database so ** remember to backup first and try after hours if possible ** - hope this helps - I look forward to additional posts on your issue that may improve my sql - thanks

"Jas> I just started doing some sales by supplier reports, but realized that my

Here are 2 queries that will do the job.

1. Run this query first and it will update the SupplierID of the child from the parent if there is not one already set

UPDATE Item_1 SET Item_1.SupplierID = Item.SupplierID FROM Item AS Item_1 INNER JOIN Item ON Item_1.ParentItem = Item.ID WHERE Item_1.SupplierID = 0

1. Run this query second to insert the ItemID and SupplierID of ANY items that have a SupplierID and they do not exist in the SupplierList table

INSERT INTO SupplierList (Item.ItemID, Item.SupplierID) SELECT Item.ID as ItemID, Item.SupplierID FROM Item LEFT JOIN SupplierList ON (Item.ID = SupplierList.ItemID) AND (Item.SupplierID = SupplierList.SupplierID) WHERE (((Item.SupplierID) Is Not Null And Not (Item.SupplierID)=0) AND ((SupplierList.ItemID) Is Null) AND ((SupplierList.SupplierID) Is Null))

(If you need the second query to specifically only insert for child items please post back)