# 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:

a1 , a2 , a3 , a4

b1 , b2 , b3 , b4

'

So, sum up b1, b2, b3, grouping by 'b4'. Plug these back into a4 to get

a1 , a2 , a3 , a4

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