Re: Cursor Loop How to

Paul wrote:
>
> I have a table that has a time stamp stored as a text field. The
> problem is that I have several hundred that are the same.
>
> I know who to find them.
> SELECT Time_Stamp FROM Time_Table WHERE Create_Time = '1200000000'
>
> How to I find them and then add +1 to each of them progressively so that
> they all the records have a different time stamp.
>
> I do want to see the records that would be changed before I run the
> actual updating statement, so I want to use a select statement.
>
> I have know that the cursor when using a fetch statement needs a select
> into to work. This approach does not let me see what the changes would
> be prior to running the SQL.
>
> I have rights to update records in an existing table, but can't create a
> new table.
>
> Example:
> Original Revised
> 1200000000 1200000000
> 1200000000 1200000001
> 1200000000 1200000002
> 1200000000 1200000003
> 1200000000 1200000004
>
> Help Me Please.
>

Hope it helps:

DECLARE
 i NUMBER(20)
 rr VARCHAR2(20);
 CURSOR c1 IS SELECT rowid
   FROM Time_Table WHERE Create_Time = '1200000000'; BEGIN
  SELECT MIN(TO_NUMBER(Time_Stamp)) INTO i

        FROM Time_Table WHERE Create_Time = '1200000000';   OPEN c1;
  LOOP
    FETCH c1 INTO rr;
    EXIT WHEN c1%NOTFOUND;
    UPDATE Time_Table SET Time_Stamp = TO_CHAR(i) WHERE rowid = rr;     i := i + 1;
  END LOOP;
  CLOSE c1;
  COMMIT;
END; Janusz Pawlinka
jpawl_at_wasko.gliwice.pl Received on Thu Jul 22 1999 - 04:21:48 CDT