Post reply

Help writing an Update Stmt that contains an Aggregate

  • July 23, 2008 at 7:35 am

    #196005

    I wrote the following code as a select stmt to verify i had the correct result set and it ran fine, but when I converted it to an update stmt it gave me this error msg: An aggregate may not appear in the set list of an UPDATE statement.

    Can someone please help me convert this to an update stmt.

    declare @iDateID int

    set @iDateID = 20080721

    UPDATE Newton.dbo.TempCustomerCancel_test

    SET --select

    Mto1Flag = CASE WHEN (COUNT(ModifiedHeaderNumber) > 1) THEN ModifiedHeaderNumber ELSE 0 END

    -- ,ModifiedHeaderNumber

    FROM Newton.dbo.TempCustomerCancel_test WITH (NOLOCK)

    WHERE OriginalCancellationDateID = @iDateID

    --group by ModifiedHeaderNumber

    --order by ModifiedHeaderNumber

  • July 23, 2008 at 8:39 am

    #847090

    well, How about splitting the select and update statements. Declare another variable, select your aggregate data into the variable and use the data to update the table.

    If you have multiple values to update, you could either use a temporary table and select aggregate values into the table and use a join update

    or

    use a CTE for the aggregate values followed by an update statement using the CTE. see example for CTE below.

    Use CTE for aggregate update

    with xyz as

    (

    select count(AutoMaster.Make) Cnt, Make from automaster group by Make

    )

    update makecount

    set NoOfVehicles = Cnt

    from MakeCount inner join xyz on xyz.Make = makecount.Make

    good luck

    chandra

  • July 23, 2008 at 8:43 am

    #847092

    thank you

  • July 23, 2008 at 8:50 am

    #847098

    Glad to help

Viewing 4 posts - 1 through 3 (of 3 total)

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