Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: Update statement.

    Hello,

    I am trying to update three fields in a tbl (c) from the sum of another tbl (b).
    But also tbl (b) also needs to be joined to tbl (a) to meet a criteia.

    I also do not want lengthy statement. Could someone tell me what I am doing wrong with my statement below?

    UPDATE DBO.HIST3
    SET INTRA_WGT = SUM(DBO.HIST2.EXT_TUBE_WGT),
    INTRA_MKT = SUM(DBO.HIST2.EXT_MKT_AMT),
    INTRA_NET = SUM(DBO.HIST2.GROSS_AMOUNT)
    WHERE DBO.HIST3.SHIP_PLANT,DBO.HIST3.BILL_DATE IN

    (SELECT DBO.HIST2.SHIP_PLANT,DBO.HIST2.BILL_DATE
    FROM DBO.HIST2
    INNER JOIN DBO.HIST1 ON
    DBO.HIST2.INVOICE_NBR = DBO.HIST1.INVOICE_NBR
    WHERE INVOICE_TYPE = 'IP'
    GROUP BY DBO.HIST2.BILL_DATE ,DBO.HIST2.SHIP_PLANT)

    The table (Hist3) and (Hist2) needs to be joined by two fields to get correct results, but I get the following error msgs.
    Msg: An aggregate may not appear in the set list of an UPDATE statement.

    Any advise?

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    Something like this should get it. Basically do the Aggregate in a Sub Query and then use that in the Set list. (obviously untested, but it parsed correctly. )


    Code:
    
    UPDATE H3
    SET INTRA_WGT = H2.INTRA_WGT,
     INTRA_MKT = H2.INTRA_MKT,
     INTRA_NET = H2.INTRA_NET
    FROM DBO.HIST3 H3
     JOIN(
       SELECT DBO.HIST2.SHIP_PLANT,
         DBO.HIST2.BILL_DATE,
         SUM(DBO.HIST2.EXT_TUBE_WGT) AS INTRA_WGT,
         SUM(DBO.HIST2.EXT_MKT_AMT) AS INTRA_MKT,
         SUM(DBO.HIST2.GROSS_AMOUNT) AS INTRA_NET
       FROM DBO.HIST2
         INNER JOIN DBO.HIST1 ON DBO.HIST2.INVOICE_NBR = DBO.HIST1.INVOICE_NBR
       WHERE INVOICE_TYPE ='IP'
       GROUP BY DBO.HIST2.BILL_DATE ,DBO.HIST2.SHIP_PLANT
     ) H2 ON H3.SHIP_PLANT = H2.SHIP_PLANT AND H3.BILL_DATE = H2.BILL_DATE
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Feb 2008
    Posts
    2
    That worked, thank you very much!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •