Re: Multi column update

From: Sashi <smalladi_at_gmail.com>
Date: Wed, 11 Nov 2009 15:32:56 -0800 (PST)
Message-ID: <84065eb1-0c9c-4cea-a4db-571337d21209_at_t2g2000yqn.googlegroups.com>



On Nov 9, 5:19 pm, Sashi <small..._at_gmail.com> wrote:
> On Nov 9, 4:37 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
>
>
> > On Nov 9, 4:21 pm, Sashi <small..._at_gmail.com> wrote:
>
> > > I should've been more careful with my post, I guess.
>
> > > select sum(b1), sum(b2), sum(b3)
> > > from B
> > > group by b4
>
> > > Would yield a table.
> > > Now I want to join that temp table to my my table 'A' and update three
> > > columns in A, which joining a4 with b4.
>
> > > Example
>
> > > Table A:
> > > ------------------------------------------
> > > a1    , a2    , a3    , a4
> > > ------------------------------------------
> > > null, null,     null,    'Candy'.
> > > null, null,     null,   'Cookies'
>
> > > Tabl3 B;
> > > ------------------------------------------
> > > b1   , b2    , b3    , b4
> > > ------------------------------------------
> > > 12,    22,    .25,    'Candy'
> > > 10,    12,    .50,    'Candy'
> > > 22,    12,    .35,    'Cookies'
> > > 20,    18,    .40,    'Cookies
> > > '
> > > So, sum up b1, b2, b3, grouping by 'b4'.
> > > Plug these back into a4 to get
> > > ------------------------------------------
> > > a1    , a2    , a3    , a4
> > > ------------------------------------------
> > > 22,     34,    .75,    'Candy'
> > > 42,     30,    .75,    'Cookies'.
>
> > > Hope this is a good example.
>
> > > TIA,
> > > Sashi
>
> > Sashi,
>
> > I think that you missed the message in my example - you need to
> > specify the WHERE keyword in the SQL statement before the GROUP BY
> > keyword.  Here is an example which more closely matches your setup:
> > CREATE TABLE T3 (
> >   A1 NUMBER,
> >   A2 NUMBER,
> >   A3 NUMBER,
> >   A4 VARCHAR2(10));
>
> > CREATE TABLE T4 (
> >   B1 NUMBER,
> >   B2 NUMBER,
> >   B3 NUMBER,
> >   B4 VARCHAR2(10));
>
> > INSERT INTO T3 VALUES(null,null,null,'Candy');
> > INSERT INTO T3 VALUES(null,null,null,'Cookies');
>
> > INSERT INTO T4 VALUES(12,    22,    .25,    'Candy' );
> > INSERT INTO T4 VALUES(10,    12,    .50,    'Candy' );
> > INSERT INTO T4 VALUES(22,    12,    .35,    'Cookies' );
> > INSERT INTO T4 VALUES(20,    18,    .40,    'Cookies' );
>
> > COMMIT;
>
> > SELECT
> >   *
> > FROM
> >   T3 A;
>
> >         A1         A2         A3 A4
> > ---------- ---------- ---------- -------
> >                                  Candy
> >                                  Cookies
>
> > In the above, note that T3 is your table A, and T4 is your table B.
> > Now if I try to use the SQL statement you provided, I receive an
> > error:
> > update T3 A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from T4 B
> > group by b4 where B.b4 = A.A4);
>
> > ERROR at line 2:
> > ORA-00907: missing right parenthesis
>
> > If I try again, this time placing the WHERE clause before the GROUP BY
> > clause:
> > update T3 A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from T4 B
> > where B.b4 = A.A4 group by b4);
>
> > 2 rows updated.
>
> > The SQL statement worked.
>
> > SELECT
> >   *
> > FROM
> >   T3 A;
>
> >         A1         A2         A3 A4
> > ---------- ---------- ---------- -------
> >         22         34        .75 Candy
> >         42         30        .75 Cookies
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Charles,
>
>
>
> > On Nov 9, 3:03 pm, Sashi <small..._at_gmail.com> wrote:
> > > Hi all, I'm trying to write where appears to be a simple query but
> > > can't get it right.
> > > Here's the intent.
> > > update A set (a1,a2,a3) = (select sum(b1),sum(b2),sum(b3) from B group
> > > by b4 where B.b4 = A.A4)
> > > I got two tables, A and B. The join parameters are A.A4 and B.B4.
> > > I need to updated a1, a2 and a3 in A to equal the sums of b1, b2 and
> > > b3 in B. The sum needs to be grouped by b4 and A.a4 needs to equal
> > > B.b4.
> > > Can someone help?
> > > TIA,
> > > Sashi
> > It appears that you have the GROUP BY and WHERE clause in the wrong
> > order - at least that is the problem that I see.  An example:
> > SELECT
> >   1
> > FROM
> >   DUAL;
> >          1
> > ----------
> >          1
> > SELECT
> >   1
> > FROM
> >   DUAL
> > GROUP BY
> >   1;
> >          1
> > ----------
> >          1
> > SELECT
> >   1
> > FROM
> >   DUAL
> > WHERE
> >   1=1;
> >          1
> > ----------
> >          1
> > SELECT
> >   1
> > FROM
> >   DUAL
> > GROUP BY
> >   1
> > WHERE
> >   1=1;
> >          1
> > ----------
> >          1
> > SQL> SELECT
> >   2    1
> >   3  FROM
> >   4    DUAL
> >   5  GROUP BY
> >   6    1
> >   7  WHERE
> >   8    1=1;
> > WHERE
> > *
> > ERROR at line 7:
> > ORA-00933: SQL command not properly ended
> > (Note that the above is the syntax order you are attempting to use)
> > SELECT
> >   1
> > FROM
> >   DUAL
> > WHERE
> >   1=1
> > GROUP BY
> >   1;
> >          1
> > ----------
> >          1
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Charles,
> I should've been more careful with my post, I guess.
>
> It works like a charm.
> Thanks for your time.
>
> Sashi

The sub-query runs in about 90 seconds and comes up with a result set with 132 records. In my example, this would be the B table. The A table also has the same number of records. However, the update runs for about 22 minutes. Since the subquery runs rather fast (and it has to go thru about 9 million rows to come up with this summary), I'm surprised that the update should take so long.
The example I've given is a sort of simplified version of my query, but it's a very good approximation.
i.e., select sum(b1), sum(b2), sum(b3) from B group by b4 runs in less than 90 seconds.
So it's safe to assume that the join condition is causing the inefficiency.
Any ideas on how to make it run quicker? Thanks,
Sashi Received on Wed Nov 11 2009 - 17:32:56 CST

Original text of this message