Re: Updating one table from another

SQL should be very similiar. Maybe it's the get date thats killing you, not an oracle function. Oracle uses sysdate. You would want:

update sometable_a set total_pts = total_pts + b.points_earned from sometable_b b where id = b.id and
trunc(b.date_input) = trunc(sysdate);

I use trunc cause it rounds the date and ignores minutes and seconds.

In article <8f77pj$ct$1_at_nnrp1.deja.com>,   Tina Mancuso <tina_mancuso_at_yahoo.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 08 2000 - 00:00:00 CDT