Updating one table from another

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.