Post reply

Updating a table while reiterating thru it

  • April 2, 2002 at 7:52 pm

    #101111

    I can't seem to figure out how to do this, even though I know it is pretty easy. Driving me nuts though.

    Supposed I have two tables:

    Product_Categories

    CatID | ParentID | Name

    and

    Product

    ID | CatID | ProductName

    with CatID referring to CatID in table Product_Categories. Now what I want to do is to update table Product so that it updates the CatID to its Parent categories ID

    So I could write it like this, for a single row

    Update Product set CatID=(Select ParentID from Product_Categories where CatID=Product.CatID) where ID=[Some_Number]

    Now what I can't seem to do is to write a stored proc so that it reiterates through the Product table and does it for each row.

    Any help is appreciated. Thanks guys!

    Scott.

  • April 3, 2002 at 12:00 am

    #426510

    If you really wanted to run that particular statement through the whole table, you could write a Stored Proc with a WHILE loop in it that plugged your index variable into the spot you mark with "[Some_Number]". However, if the table's not too big, you're probably better off running a single UPDATE statement (not inside of a loop) that does the whole thing. It would look like:

    

    UPDATE Product

    SET CatID = c.ParentID

    FROM Product_Categories c

    WHERE Product.CatID = c.CatID

    See Steve Jones' article 'Divide and Conquer' (http://www.sqlservercentral.com/columnists/sjones/batching.asp), which I just finished reading, for related discussion.

    -john.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply