Re: Updating tables using cursor, I need help

In article <3567D093.F8CCF3FA_at_u.washington.edu>,   mikkro_at_hbsi.com wrote:
>
> 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
>
> Thanks Micheal

I have tried your suggestion, the update part doesn't do anything at all. But I got an idea from someone over the internet. It works partially not fully here it is what is is failing is inser part

for x in (select * from table1 ) loop

     begin
       update table2
       set col1 = ...
      if (sql%rowcount = 0 ) then
          insert into table2 values (x.col1)
      end if;
exception
      when others then
            update table1
            set flag = 'Y'
            where col1 = x.col1;
       end
       end loop;

the exception part doesn't work.

an suggestion!!!

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue May 26 1998 - 00:00:00 CDT