Re: Updating one table from another

Try something like this

Update sometable_a a set total_pts = total_pts +

     (Select points_earned from sometable_b b where id = a.id and b.date_input = getDate)

Tina Mancuso wrote in message <8f77pj$ct$1_at_nnrp1.deja.com>...
>Hi,
>
>I'm a relatively new Oracle developer -- been using it for about 2
>months; before that I only used Sybase. I have looked in all the books
>and documentation that I could find, but have been unable to come up
>with a solution to my problem, so I hope someone can help.
>
>I am trying to update one table based on info in a second table. In
>Sybase, the SQL statement for what I'm trying to do would look something
>like this:
>
>update sometable_a set total_pts = total_pts + b.points_earned
>from sometable_b b where id = b.id and
>b.date_input = getdate
>
>...where "id" would be the primary key on "sometable_a" and part of the
>primary key on sometable_b. Basically, I'm trying to take a master
>table (sometable_a) and add daily totals (stored in sometable_b) to it
>each day. (getdate is Sybase's equivalent of sysdate.)
>
>So if I had these values in "sometable_a":
>
>id total_pts
>1 6
>4 8
>9 0
>
>...and these values in "sometable_b":
>
>id points_earned date_input
>1 7 05/08/2000
>1 2 05/08/2000
>4 10 05/08/2000
>9 6 05/06/2000
>
>...and today is 05/08/2000, the update should result in 15 total_pts for
>id "1" and 18 for id "4" in sometable_a. ID "9" should be unchanged
>because the date_input does not match today's date.
>
>Anyone know a quick way to do this in Oracle? I tried to put it in a
>Perl script (using DBI) and do it as a loop -- selecting the rows I need
>to change and updating them one at a time -- but that's really slow. If
>possible, I'd like to do the update in one statement.
>
>Thanks,
>--Tina
>--
>tina_mancuso_at_yahoo.com, tina_at_monster-island.org
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon May 08 2000 - 00:00:00 CDT