Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: tricky update-stmt

Re: tricky update-stmt

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: 2000/08/08
Message-ID: <398FFDF7.24BE@yahoo.com>#1/1

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 worse
Received on Tue Aug 08 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US