Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Update using group by and aggregate function

    I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value.

    UPDATE CIRSUB_M
    SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) +
    (SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M
    WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
    GROUP BY PUB_CDE, DNR_NBR)
    WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')

    Example

    PUB_CDE DNR_NBR COPIES TRM_DMO
    THN 000000092637 100 A
    THN 000000092637 200 B
    THN 000000082455 100 A
    THN 000000082455 200 B
    THN 000000051779 100 A

    Updated
    THN 000000092637 100 A300
    THN 000000092637 200 B300
    THN 000000082455 100 A300
    THN 000000082455 200 B300
    THN 000000051779 100 A100

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to INNER JOIN your SUM(COPIES) sub-select, rather than concatenate it directly. You'd still use PLUS-sign, but on the field from the JOIN, rather than the entire sub-select.

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Talking

    The query below worked.

    UPDATE CIRSUB_M
    SET TRM_DMO = LEFT(TRM_DMO,11) + U.TOTAL_COPIES
    FROM CIRSUB_M C1
    JOIN (SELECT C2.DNR_NBR, C2.PUB_CDE, CAST(SUM(C2.COPIES) AS VARCHAR(5))TOTAL_COPIES
    FROM CIRSUB_M C2
    WHERE C2.BIL_ORG = '02' AND C2.CRC_STS IN ('R','P','Q','T')
    GROUP BY C2.PUB_CDE, C2.DNR_NBR) AS U
    ON C1.DNR_NBR = U.DNR_NBR
    AND C1.PUB_CDE = U.PUB_CDE
    WHERE C1.BIL_ORG = '02'
    AND C1.CRC_STS IN ('R','P','Q','T')

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    See? Nothing is impossible, huh?!

  5. #5
    Join Date
    Dec 2009
    Location
    India
    Posts
    1
    Hi PPATEL,
    Seems you also use Advantage CRM.
    Which version do you use and which company you are in?

    Thanks,
    MP

Posting Permissions

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