Re: Multi column update
Date: Mon, 9 Nov 2009 13:04:59 -0800 (PST)
Message-ID: <c1c61598-05d9-48f2-8916-59e595e844ec_at_p35g2000yqh.googlegroups.com>
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.
Received on Mon Nov 09 2009 - 15:04:59 CST