Re: Multi column update

From: Charles Hooper <hooperc2000_at_yahoo.com>
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

Original text of this message