locked
UPDATE Statement with a JOIN Condition RRS feed

  • Question

  • Hi all,

    HERE IS MY UPDATE STATEMENT:

    Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM

    WACTS_Inventory_Part_Loc_Tbl WIPLT

    INNER JOIN

    Temp_MatIn_Data tmp

    ON

    WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID

    WHERE

    WIPLT.WIPLT_Location_Code='MF'

     

    I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.

    The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.

     

    Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.

    I woould appreciate any help on this.

     

    Thanks,

    Jothi

     

    Friday, June 30, 2006 8:29 PM

Answers

  • UPDATE statement can affect each qualifying row only once even if the use of JOIN produces multiple rows for the target table in the UPDATE statement. This is an artifact of the proprietary UPDATE syntax and you should avoid it as far as possible due to these issues & others. Instead write your UPDATE statement like:
     
    Update WACTS_Inventory_Part_Loc_Tbl
    SET WIPLT_Part_New_Qty = WIPLT_Part_New_Qty +
    (SELECT SUM(tmp.MATIN_QTY)
    FROM Temp_MatIn_Data AS tmp
    WHERE tmp.PARTS_ShortID = WACTS_Inventory_Part_Loc_Tbl.WIPLT_Part_Number_MSBA)
    WHERE WIPLT_Location_Code='MF'
     

    The limitations of the TSQL specific UPDATE statement is also documented in the UPDATE statement topic in BOL.
    Friday, June 30, 2006 8:57 PM

All replies

  • UPDATE statement can affect each qualifying row only once even if the use of JOIN produces multiple rows for the target table in the UPDATE statement. This is an artifact of the proprietary UPDATE syntax and you should avoid it as far as possible due to these issues & others. Instead write your UPDATE statement like:
     
    Update WACTS_Inventory_Part_Loc_Tbl
    SET WIPLT_Part_New_Qty = WIPLT_Part_New_Qty +
    (SELECT SUM(tmp.MATIN_QTY)
    FROM Temp_MatIn_Data AS tmp
    WHERE tmp.PARTS_ShortID = WACTS_Inventory_Part_Loc_Tbl.WIPLT_Part_Number_MSBA)
    WHERE WIPLT_Location_Code='MF'
     

    The limitations of the TSQL specific UPDATE statement is also documented in the UPDATE statement topic in BOL.
    Friday, June 30, 2006 8:57 PM
  • you can  make use of the aggregate function sum() to get the total qty

    Friday, June 30, 2006 8:57 PM
  • Nice post Mr. Jayachandran. Helped me a lot. Greetings from Venezuela (land of world famous banana dictator)

     

    Sunday, June 8, 2008 8:24 PM