Re: Multi column update

From: Sashi <smalladi_at_gmail.com>
Date: Mon, 9 Nov 2009 14:19:15 -0800 (PST)
Message-ID: <0824b78e-60b8-4f69-bb6a-cfb0c2be5941_at_l13g2000yqb.googlegroups.com>



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 Received on Mon Nov 09 2009 - 16:19:15 CST

Original text of this message