Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Updating and aggregating multiple rows in one table from another table

    I need to update multiple rows in Table1 from data in Table2 as follows-

    I have a query that returns multiple rows from Table2 by sum and grouping. The results of that sub-query need to be added(aggregated into rows in Table1)


    Table 1

    val id
    A1 1
    A2 2
    A3 3


    Table 2(rows returned as the result of the sub-query)

    val id
    A1 1
    A2 2
    A3 3
    so values in table1 need to be updatedas

    table1.A1 = table1.A1+table2.A1
    table1.A2 = table1.A2+table2.A2

    .... and so on.

    My subquery returns multiple rows-
    select round (sum(table2.X1)+sum(table2.X2)+sum(table.X3))
    from table2
    where (...)
    group by table2.gid

    So I want to update values in T1.val with these rows where T1.id = T2.id.
    But if I use UPDATE statement, I get an error saying 'multiple rows returned by query'. How do I get around this. Please help!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, by avoiding the TOO-MANY-ROWS error.

    It would be nice if you provided test case (CREATE TABLE and INSERT INTO sample data, so that we could see what you have, as well as expected output). Information you provided is somewhat insufficient (maybe someone is capable to help; I prefer to know the enemy).

  3. #3
    Join Date
    Jun 2011
    Posts
    2

    reply-more details

    create table2
    (X1 NUMBER(10),
    X2 NUMBER (10),
    X3 NUMBER(10),
    gid NUMBER(10)
    )

    gid is not unique, so the tablecould have data

    gid X1 X2 X3

    1 10 10 15
    1 10 3 1
    2 4 4 9
    3 10 3 1
    2 10 3 10

    Based on the above data, the query needs to sum the data in coloums X1.X2 and X3 grouped on gid and generate data for table T1 such that it would hold...

    here gid IS unique
    Table T1

    gid gsum(sum(X1)+sum(X2)=sum(X3)) group by gid - from Table T2)

    1 39
    2 40
    3 14
    Last edited by codingjunkie; 06-02-11 at 03:45. Reason: better readability

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    As the error you report is caused by UPDATE statement, I wonder why you did not post it here instead of those various code snippets.

    My guess would be you are not correlating tables T1 and T2 in that UPDATE statement. You may find the correct way of doing it e.g. in this thread: http://www.dbforums.com/oracle/16299...mn-update.html

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm sorry, but I still don't understand what you are saying. Now we know how T1 table looks like, as well as the fact that you want to sum column values per GID (BTW, either your sum for GID = 1 is wrong, or you posted wrong values for T1 contents). (BTW2, you *forgot* to include INSERT INTO statements).

    What is "from Table T2" supposed to mean? Where is that table T2? How does it look like? Is anything stored in there, or do you have to fill it?

    What next?

    How to ask questions the smart way? might be worth reading.

  6. #6
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    that should be done in a simple update...

    paste the update sql u tried and lets see what the errror is
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Tags for this Thread

Posting Permissions

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