Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Question Unanswered: Update using joins in Oracle pl/sql

    update Table1 t1 set t1.c1 = (sum(t2.c1) from Table2 t2
    where t1.c2 = t2.c2 and
    t1.c3 = t2.c3 and t1.c4 = to_date('10/23/2001','mm/dd/yyyy'));

    I'm trying to update Table t1 (c1) with the sum of Table2 columns sum(c1), with c2 and c3 columns being in the join and for a particular date. I want to do a update here in one shot. The subquery returns more than 1 row. Typically 10 rows for 10 different c1 values. Now I want to update those 10 corresponding c1 rows in table T1 at one shot by using a query like above.
    T1 is the parent table and T2 is child table here.
    I know this works well in MQ SQL Server. But not sure whether it works in Oracle 8.1.7 pl/sql or not. Please advise.

    Thanks,
    Gopal

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Almost. Try:
    Code:
    update Table1 t1
       set t1.c1 = (select sum(t2.c1)
                      from Table2 t2
                     where t1.c2 = t2.c2
                       and t1.c3 = t2.c3)
     where t1.c4 = to_date('10/23/2001','mm/dd/yyyy');
    Note however that this will set t1.c1 to NULL if there are no matching t2 records at all. If you want to avoid updating those t1 records, you need a further restriction:
    Code:
    update Table1 t1
       set t1.c1 = (select sum(t2.c1)
                      from Table2 t2
                     where t1.c2 = t2.c2
                       and t1.c3 = t2.c3)
     where t1.c4 = to_date('10/23/2001','mm/dd/yyyy');
       and exists (select null
                     from Table2 t2
                    where t1.c2 = t2.c2
                      and t1.c3 = t2.c3);

  3. #3
    Join Date
    Aug 2003
    Posts
    14
    update Table1 t1
    set t1.c1 = (select sum(t2.c1)
    from Table2 t2
    where t1.c2 = t2.c2
    and t1.c3 = t2.c3)
    where t1.c4 = to_date('10/23/2001','mm/dd/yyyy');

    In the above query the select statement returns more than 1 row. 10 different values and I want to update those 10 diff values of t2.c2 with the t1.c1 10 rows. When I tried this I get an error subquery returned more than 1 row.

    I tried in a different way like this .
    update (
    select t1.c2, t1.c1 FundVal, sum(t2.c1) FundAggr from
    table1 t1, table2 t2
    where t1.c2 = t2.c2 and
    t1.c4 = to_date('10/23/2001','mm/dd/yyyy') group by t1.c2, t1.c1
    ) set FundVal = FundAggr

    But I got an error
    ORA-01732 Data Manipulation operation not legal on this view.
    There are no views involved here at all. Both t1 and t2 are tables.
    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by g0pal
    update Table1 t1
    set t1.c1 = (select sum(t2.c1)
    from Table2 t2
    where t1.c2 = t2.c2
    and t1.c3 = t2.c3)
    where t1.c4 = to_date('10/23/2001','mm/dd/yyyy');

    In the above query the select statement returns more than 1 row.
    No it doesn't! How can "select sum(x)" without a GROUP BY clause return more than one row? Did you actually try running the statement?

  5. #5
    Join Date
    Aug 2003
    Posts
    14
    I'm sorry. It does have a group by clause and I forgot to mention that.
    The query is like this...

    update Table1 t1
    set t1.c1 = (select sum(t2.c1)
    from Table2 t2
    where t1.c2 = t2.c2
    and t1.c3 = t2.c3)
    where t1.c4 = to_date('10/23/2001','mm/dd/yyyy')
    group by t1.c2;

    Well here the intent is to update 10 rows or whatever that are there for that where clause (as an example) for that c1 value on table t1 with the sum of c1 columns of table t2 for the given c2 and c3 columns betn the 2 tables.
    Please advice.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by g0pal
    I tried in a different way like this .
    update (
    select t1.c2, t1.c1 FundVal, sum(t2.c1) FundAggr from
    table1 t1, table2 t2
    where t1.c2 = t2.c2 and
    t1.c4 = to_date('10/23/2001','mm/dd/yyyy') group by t1.c2, t1.c1
    ) set FundVal = FundAggr

    But I got an error
    ORA-01732 Data Manipulation operation not legal on this view.
    There are no views involved here at all. Both t1 and t2 are tables.
    Thanks
    The view referred to here is the "inline view" corresponding to the query:
    Code:
     select t1.c2, t1.c1 FundVal, sum(t2.c1) FundAggr from 
     table1 t1, table2 t2
     where t1.c2 = t2.c2 and 
     t1.c4 = to_date('10/23/2001','mm/dd/yyyy') group by t1.c2, t1.c1
    It is that inline view that you are trying to update, and apparently it isn't possible. Some views are updateable, but not all: Oracle has to be able to see how to automatically convert the statement to an update on the appropriate table. I believe that any view with aggregate functions is not updateable, but I'm not 100% sure.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Remove the GROUP BY and it will work. It will work like this (conceptually, not exactly):
    Code:
    for rec in (select t1.*, t1.rowid from Table1 t1 
                where t1.c4 = to_date('10/23/2001','mm/dd/yyyy')) 
    loop
      select sum(t2.c1)
      into v_sum
      from Table2 t2
      where t2.c2 = rec.c2
      and t2.c3 = rec.c3;
      
      update Table1 t1
      set t1.c1 = v_sum
      where t1.rowid = rec.rowid;
    end loop;

  8. #8
    Join Date
    Aug 2003
    Posts
    14
    I tried removing the sum function and just kept the column name t2.c1 instead of sum(t2.c1) in that select statement but the result was same. I got the same errror. ORA-01732

  9. #9
    Join Date
    Aug 2003
    Posts
    14
    Does this mean that I can't update multiple rows in a table in one shot in oracle pl/sql? I remember to have done this in MS SQL Server earlier.
    Oracle sql doesn't support such multiple updates?

    Should I give up exploring more on this and go back to doing it programmatically as you suggested or in some other way using JDBC?
    Please advice.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by g0pal
    I tried removing the sum function and just kept the column name t2.c1 instead of sum(t2.c1) in that select statement but the result was same. I got the same errror. ORA-01732
    Also, Table1 would need to be "key-preserved" in that view. That will only be true if there is a foreign key on Table1 referencing Table2. In your case, that presumably won't be true - if anything the foreign key will be the other way around. So updating the select statement is not an option.

    I still don't see why you can't use the update I gave you earlier:
    Code:
    update Table1 t1
       set t1.c1 = (select sum(t2.c1)
                      from Table2 t2
                     where t1.c2 = t2.c2
                       and t1.c3 = t2.c3)
     where t1.c4 = to_date('10/23/2001','mm/dd/yyyy');
    Copy and past that, and see what happens. Note that there is no GROUP BY anywhere in that SQL, nor should you add one!

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by g0pal
    Does this mean that I can't update multiple rows in a table in one shot in oracle pl/sql? I remember to have done this in MS SQL Server earlier.
    Oracle sql doesn't support such multiple updates?

    Should I give up exploring more on this and go back to doing it programmatically as you suggested or in some other way using JDBC?
    Please advice.
    No, I have shown you precisely how to update multiple rows in one shot; I just repeated the SQL in my previous post, so I won't do it again now.

    Also, I did not suggest that you do it programmatically: I gave some PL/SQL code merely to illustrate what Oracle does "under the covers" (more or less) when you issue the update statement I suggested. I didn't intend you to run that code!

  12. #12
    Join Date
    Aug 2003
    Posts
    14
    The group by clause is the key here. Because there are 10 diff values for t1.c1.
    Code:
    t1.c2  t1.c1          t2.c2    t2.c1
    v1                       v1      10
    v2                       v1      15 
    v3                       v2      20 
                             v2      30 
                             v2      15
                             v3      25
    After the update t1 should have values like this.

    Code:
    t1.c2  t1.c1
    v1      25
    v2      65
    v3      25
    c2 is the primary key in table t1. c2 in table t2 referes to it.(foreign key)
    And I was trying to do this using just one update statement.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    And as I keep on telling you, yes, you can do it in one update statement - the very update statement I keep posting and you keep ignoring!!! Let me actually do it for you:
    Code:
    SQL> select * from table1;
    
    C2         C1
    -- ----------
    v1
    v2
    v3
    
    SQL> select * from table2;
    
    C2         C1
    -- ----------
    v1         10
    v1         15
    v2         20
    v2         30
    v3         25
    
    SQL> update table1 t1
      2  set c1 = (select sum(c1)
      3              from table2 t2
      4             where t2.c2 = t1.c2);
    
    3 rows updated.
    
    SQL> select * from table1;
    
    C2         C1
    -- ----------
    v1         25
    v2         50
    v3         25
    Do you see it now?

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You do NOT need the group by clause on an update!!!!! Use the following code that Tony gave you.

    update Table1 t1
    set t1.c1 = (select sum(t2.c1)
    from Table2 t2
    where t1.c2 = t2.c2
    and t1.c3 = t2.c3)
    where t1.c4 = to_date('10/23/2001','mm/dd/yyyy');

    The select sum(t2.c1)... will return only one row (do NOT get rid of the sum) and the update will set EVERY row in t1 that has a date of 10/23/2001 in c4 to either a sum(c1) or a null if no matching records are found in t2. You do NOT use a group by in an update. An update by it's vary nature operates on one row at a time!!!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Aug 2003
    Posts
    14

    Smile

    Yes Tony, it did work, thanks so much. I'm sorry for being so ignorant...
    Thank you so much!!
    Gopal

Posting Permissions

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