Re: Multi column update

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 9 Nov 2009 13:37:37 -0800 (PST)
Message-ID: <99bc7e81-dcb3-44c8-b384-db903ccf28a4_at_g23g2000yqh.googlegroups.com>

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