SQL Server Performance

Aggregate function in Update statement

Discussion in 'General Developer Questions' started by eramgarden, Jun 27, 2006.

  1. eramgarden New Member

    I thought I could do the SQL below but I get a msg that Aggregate function is not allowed in Update. How can I do this then?



    Update ColHst set
    H1Pl$T =
    case when H1PL$T + Sum(AmountIn) >= 0 then H1PL$T + Sum(AmountIn)
    ELSE 0
    end ,
    from cCollInfo, COLHST
    where H1COL=CollID
    and H1SSNO=ColSSN
    and H1Year=dsYear
    and H1Mon=dsMonth
    and OldNew='N'

    group by H1Col, H1SSNO, H1Year, H1Mon,H1Pl$T,H1PL$M,H1PL#T,H1PL#M





  2. shinoj_r New Member

    Can we not use correlated subquery. Here is a sample.

    CREATE TABLE #tmp (fld1 INT, H1Pl$T INT, AmountIn INT)
    INSERT INTO #tmp
    SELECT 1, 100, 1000 UNION ALL
    SELECT 1, 100, 1000 UNION ALL
    SELECT 1, 100, 1000 UNION ALL
    SELECT 1, 100, 1000 UNION ALL
    SELECT 2, 300, 5000 UNION ALL
    SELECT 2, 300, 5000

    -- Update Statement
    UPDATE T1
    SET H1Pl$T =
    (SELECT CASE WHEN H1Pl$T + SUM(AmountIn) > 0 THEN SUM(AmountIn) ELSE 0 END FROM #tmp T2 WHERE T2.fld1 = T1.fld1 GROUP BY T2.fld1, H1Pl$T)
    FROM #tmp T1

    DROP TABLE #tmp

    Thanks
    Shinoj R
  3. Madhivanan Moderator

    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail

Share This Page