Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: tricky update-stmt
Tom wrote:
>
> 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;
Use a linear congruential random number generator - these can be chosen
as such to provide a non-repeating set of "random" numbers...
HTH
-- =========================================== Connor McDonald http://www.oracledba.co.uk We are born naked, wet and hungry...then things get worseReceived on Tue Aug 08 2000 - 00:00:00 CDT