Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Programming in SQL?
I presume you are summing on related keys. Try this
UPDATE TABLE A
SET AMOUNT = SELECT AMT FROM
SELECT KEY_VALUES, SUM(AMOUNTS) AMT FROM TABLE B WHERE A.KEY_VALUES = B.KEY_VALUES)
You will have to tweak the above to get what you want. The idea is to use the result set returned by a SELECT like a table, instead of creating a view or using PL/SQL. This works on Oracle 8i.
<amerar_at_ci.chi.il.us> wrote in message news:7v4onm$bd7$1_at_nnrp1.deja.com...
>
>
> Hello,
>
> I am trying to create a very interesting query and I'm not sure if it
> can be done.
>
> I have two tables: Table A and Table B. There is a one to many
> relationship from Table A to Table B. What I want to do, for the dollar
> amount fields, is for matching records in the tables, I want sum the
> records in Table B first, then add it to the field in Table A.
>
> So,
>
> Table A has one record with a dollar amount of $100.
> Table B has three records with dollar amounts $100, $100 and -$100.
>
> I want to sum those records in Table B BEFORE adding them to Table A.
>
> Can this be done or am I stuck writing a PL/SQL procedure?
>
> Please send a copy to my e-mail.
>
> Thanks,
>
> Arthur
> amerar_at_ci.chi.il.us
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Oct 27 1999 - 09:29:34 CDT