Re: Multi column update

From: Sashi <smalladi_at_gmail.com>
Date: Mon, 9 Nov 2009 13:21:42 -0800 (PST)
Message-ID: <ec2d159d-b46f-40c8-b77d-7a06b470eeb1_at_b15g2000yqd.googlegroups.com>

On Nov 9, 4:04 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

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