Help writing an Update Stmt that contains an Aggregate

  • 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

  • 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

  • thank you

  • 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