Re: table update - best way!

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 23 Aug 1999 21:59:21 +0800
Message-ID: <37C153B9.6326@yahoo.com>

Nandakumar wrote:
>
> Hi
>
> there are two table with identical table structure(field name, field
> datatype and size are same). One of the fields in tables is DATE_UPD.
>
> i want to populate data from one table to another table based on the
> DATE_UPD. Process is like,
>
> 1.take a row from first table,
>
> 2.advance the DATE_UPD of the record by 2 days,
> ( **the logic here is NOT IMPORTANT**)
>
> 3.check for the existence of the record in the second table
> ( record is made of new DATE_UPD with others fields same as in the
> first table)
>
> 4a.if record exists in the second table, add one of the fields in the
> first table, say AMOUNT, to the same field of the second table.
>
> 4b) if record does not exist, insert it into the second table.
>
> i use a cursor to fetch records from the first table.
> But it seems, a record of CURSOR%ROWTYPE can't be inserted into a
> table. I don't want to insert like 'insert into second_table values
> csr.f1 ...
> csr.f20, as number of fields are many.
>
> Is there any other way that i can FETCH cursor into a table (
> or RECORD), manipulate the date field then insert into the second table?
> If TABLE could be used, can i use EXISTS or NOT IN clauses with the
> data rows in TABLE.
>
> i would appreciate your suggestions!
> Thanks!
> --
> Nandakumar
> (N.Kumar_at_rocketmail.com)
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

If there are appropriate primary keys, you can update a join in Oracle...

ie

update ( select a.data old_data, b.data new_data

        from table1 A, table2 B
        where a.key = b.key ) 

set old_Data = new_data

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Aug 23 1999 - 08:59:21 CDT