Re: Updating one table from another

From: Tina Mancuso <tina_mancuso_at_yahoo.com>
Date: 2000/05/08
Message-ID: <8f7asr$3uo$1@nnrp1.deja.com>#1/1

Two replies in one here...first off, I should mention that we're using Oracle 7.3.4 here. Okay, first reply:

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

Nope, I was using sysdate. My example was what I'd do in Sybase, which uses getdate. I mentioned that in my original post.

> 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);

Doesn't work; I get an error, "ORA-00933: SQL command not properly ended". It apparently doesn't recognize "from" in an update statement.

Then, in article <8f79cm$2fr$1_at_nnrp1.deja.com>,   emaus081269_at_my-deja.com wrote:
> Sorry, brain fart before, didn't notice you were using a and b.
>
> Try:
>
> Update table1
> set total_points =
> (
> select amount1 + amount2
> from atable a,
> btable b
> where a.id = b.id
> )
> where date = date;

Nope, that doesn't work either, because the "date_input" column is in table b (the one that's NOT being updated). The outer query *has* to relate the "id" columns in the two tables, but if I change your statement above to:

Update table1

   set total_points =

       (
        select amount1 + amount2
          from atable a,
               btable b
         where a.id = b.id and b.date = sysdate
        )

  where id = b.id

it of course doesn't recognize "b.id" in the outer query's where clause. I need a way to relate the two tables both in the subquery and in the main where clause. This is why I've been having such a problem with this.

Thanks for trying, though. Any other thoughts??

--Tina

> > 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.
>

--
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