Re: tricky update-stmt

Well,
I f I read it right, you do not want to update the same row twice during this entire process. You can try to add a column to the table (say EDIT_FLAG) that is CHAR(1) and at the beginning of the process, set the column on all records to 'Y'.

As you go to update each row, check to see that the EDIT_FLAG = 'Y'. In your update, also set EDIT_FLAG = 'N'. This will prevent you from updating the same row twice.

Like this:

• reset EDIT_FLAG column on table UPDATE myTable SET EDIT_FLAG = 'Y';
• Begin Process UPDATE myTable SET some_other_column = update_id, EDIT_FLAG = 'N' WHERE internalid = loc_random_id AND EDIT_FLAG = 'Y';
• End Process

"Tom" <schelhor_at_fh-coburg.de> wrote in message news:398FD6C0.9A02F71D_at_fh-coburg.de...
> Hi there!
>
> I've to do a tricky update and I tried the following code. As result I
> get no error, but there's also no update made to column 'internalid'.
> explanation:
> - createRandom() returns random number between 0 and 12770
> - I've to run 6000 updates and I've to prevent, that a row is
> updated twice
> Update works when I change my update-stmt to:
> UPDATE myTable
> SET some_other_column = update_id
> WHERE internalid = loc_random_id;
> ... but now can't prevent from updating twice the same row.
>
> Is there a solution for this? Thanx in advance!
>
> Tom
>
>
> BEGIN
> DECLARE
> loc_random_id number;
> loc_test_id number;
> update_id number;
> BEGIN
> FOR I IN 0..6000 LOOP
> loc_test_id := null;
> WHILE loc_test_id IS NULL LOOP
> loc_random_id := myPackage.createRandom(12770);
> SELECT internalid INTO loc_test_id
> FROM myTable
> WHERE internalid = loc_random_id;
> END LOOP;
> update_id := loc_test_id + 20000;
> UPDATE myTable
> SET internalid = update_id
> WHERE internalid = loc_random_id;
> END LOOP;
> COMMIT;
> END;
> END;
>
Received on Tue Aug 08 2000 - 00:00:00 CDT