Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Updating tables using cursor, I need help
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