none
UPDATE query - An aggregate may not appear in the set list of an UPDATE statement.

    Question

  • I've created a simple UPDATE query as shown below

    UPDATE

    Table1
    SET Number = Sum([Count])
    FROM Table2

    I get the following error message appear

    An aggregate may not appear in the set list of an UPDATE statement.

    This is what is in each tables...


    SELECT * FROM Table1

    Hours Number
    1649630.33000001 NULL

    SELECT * FROM Table2

    Class COUNT
    AAA 16
    BMT 7
    E1A 6

    And I want the Sum of all the Table2.Count placed in the Number column of Table1...

    How can i do this?

    Tuesday, December 15, 2009 10:12 AM

Answers

  • How about this....

     

    UPDATE

     

    Table1
    SET Number = (SELECT Sum([Count]
    ) FROM Table2)

    In Vegas, you get a million mexicans standing in a line flipping "lady" cards at you.
    • Marked as answer by Sam233 Tuesday, December 15, 2009 10:18 AM
    Tuesday, December 15, 2009 10:16 AM

All replies

  • How about this....

     

    UPDATE

     

    Table1
    SET Number = (SELECT Sum([Count]
    ) FROM Table2)

    In Vegas, you get a million mexicans standing in a line flipping "lady" cards at you.
    • Marked as answer by Sam233 Tuesday, December 15, 2009 10:18 AM
    Tuesday, December 15, 2009 10:16 AM
  • what is the relation between two tables ?

    Tuesday, December 15, 2009 10:17 AM
  • If you need to add Joins, you should use Aliases for Tables.

    UPDATE T1 SET T1.extrasPrice = (SELECT SUM(T2.Price) FROM BookingPitchExtras T2 WHERE T2.pitchID = T1.ID)
    FROM BookingPitches T1;
    Sunday, February 24, 2013 9:36 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats.

    Code should be in Standard SQL as much as possible and not local dialect. What you posted is 1970's Sybase dialect and it has cardinality problems (Google it). We use MERGE in the 21-st century :)

    COUNT and SUM are aggregate functions, so of course you cannot nest them! Why do you think that “number” is a precise, clear data element name that will make your code easy to maintain?

    Your posting makes no sense. There are no keys; hours is a unit of measurement not an identifier! UPDATE is a statement, not a query, etc.

    If you are using using Table2 (awful name!) to stage data for Table1, then it mighty look like this:

    MERGE INTO Table1
    USING (SELECT foobar_class, COUNT(*)
             FROM Table2
            GROUP BY foobar_class)
           AS Source(foobar_class, foobar_cnt)
    ON Table1.foobar_class = Table2.foobar_class
    WHEN MATCHED
    THEN UPDATE Table1
       SET foobar_grand_cnt
           = foobar_grand_cnt + Source.foobar_cnt
    WHEN NOT MATCHED
    THEN UPDATE Table1
       SET foobar_grand_tot
           = foobar_grand_tot + Source.foobar_cnt;

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, February 25, 2013 12:46 AM