Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Updating tables using cursor, I need help

Re: Updating tables using cursor, I need help

From: Michael Krolewski <vandra_at_u.washington.edu>
Date: 1998/05/24
Message-ID: <3567D093.F8CCF3FA@u.washington.edu>#1/1

You might try just doing the operations in SQL

    to create table2

        insert into table2
            select distinct field1, field2, ... from table1

        update table1
                set flg = 'Y'
                where rowid in ( select  t1a.rowid from table1 t1a,
                                                            table1 t1b
                                            where t1a.col1 = t1b.col1
                                                    t1a.rowid < t1b.rowid )
        The second query is a bit ugly -- but it will select only rows
        with at least 2 rows. One row will alway be the "greatest" rowid
        and will not be updated.

    I doubt if you can guarantee the sequencing of the row in a select and the     flagging.

    Alternatively, when inserting rows into table1. Initially set the flg to 'N'

    and test for the existance of another record with the same col1.

    In either case, you will have a problem if you are deleting records. You     could delete the 'N' version of the record pair. The other record should     changed to 'N' from 'Y'. Obviously this will take some time and code to     ensure. The same could be said of updates. If col1 is changed, then the     flg is incorrect.

    Alternatively, one could determine at runtime via query if there are two rows

    with the same col1.

Mike Krolewski

myusuf_at_my-dejanews.com wrote:

> I have the following table table1, table2 . My cursor read
> table1 and insert table2 with a values based on the folowing criterion:
> test2 table is empty.Using cursor the first record 'aa' is read and
> insert to table2. if the following records are the same as the first record
> no insertion in table2.The tricky part is if the the values of records which
> has already been read and inserted into table2 appears again, all those
> records will be flaged in table1 as 'Y', that is table1 flag column will be
> updated with the value of 'Y'. the same rule applies with all records. In
> other words always the cursor has to check for values in table2 to validate
> the values. The tables and the final outputs looks as follows
>
> note : table1 colmn flg is all null before update.
>
> select * from table1;
>
> col1 flg
> aa
> aa
> aa
> bb
> bb
> aa Y
> aa Y
> cc
> cc
> bb Y
>
> table table2
> aa
> bb
> cc
>
> these errorr ecords in table1 6 ,7 and 9th
>
> thanks in advance
> Yusuf
> myusuf1_at_followme.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sun May 24 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US