Post reply

Update Cost Query --Need Your Help

  • October 13, 2011 at 6:44 pm

    #249177

    I would like to update the cost for the items in a new warehouse that has just been added, Warehouse 2. I have the exact same items in warehouse #2 that I have in warehouse #1. The question is what would my update query look like? Can I do this? I have many parts to update the cost for but here is my example. Can you help me with my query? Thank you for your help.

    INVENTORY

    Inv_Idpk Warehouse_Idfk Part_Id Description On_Hand Quantity Cost

    1 1 100 Wire cable 3 4 6.12

    2 1 101 HDM Icable 16 16 28.99

    3 1 102 Switch 8 8 14.12 4 2 100 Wire cable 0 0 0.00

    5 2 101 HDM Icable 0 0 0.00

    6 2 102 Switch 0 0 0.00

    UPDATE INVENTORY

    SET Cost = (select Part_Id, Cost from INVENTORY where WareHouse_Id = 1)

    WHERE

  • October 13, 2011 at 7:37 pm

    #1394419

    Like this...

    UPDATE inv2

    SET Cost = inv1.Cost

    FROM dbo.Inventory inv1

    INNER JOIN dbo.Inventory inv2

    ON inv2.Part_ID = inv1.Part_ID

    WHERE inv1.WareHouse_Idfk = 1

    AND inv2.WareHouse_Idfk = 2

    ;

    I'd also like to suggest that including "fk" in your column names will prove to be most inconveinient to you in the future especailly when it comes to certain automations you may wish to do in the future concerning column names.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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