Home » SQL & PL/SQL » SQL & PL/SQL » ?? tough update !!
?? tough update !! [message #19564] Thu, 28 March 2002 11:26 Go to next message
DRE
Messages: 9
Registered: March 2002
Junior Member
Hi, I have a situation where I want to randomly update 50 items in a table. My first thought was to include a random seed as part of the update like so:

update table
set somefield = 1
where rownum < 51
order by seedfield

Alas oracle chocked on the order by statement (Though the row num worked fine!) Anybody know how I could get this effect in a single query?
Re: ?? tough update !! [message #19566 is a reply to message #19564] Thu, 28 March 2002 12:30 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If you are running 8i or later:

update t
   set c = 1
 where rowid in (select rowid
                   from (select rowid 
                           from t
                          order by dbms_random.value)
                  where rownum <= 50);
Re: ?? tough update !! [message #19568 is a reply to message #19564] Thu, 28 March 2002 12:43 Go to previous messageGo to next message
DRE
Messages: 9
Registered: March 2002
Junior Member
Hi Todd, it seems to choke on the order by statement wherever we put it! This time it thinks the parentheses are unfinished? DRE
Re: ?? tough update !! [message #19570 is a reply to message #19564] Thu, 28 March 2002 13:21 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You must be on 8.0x or earlier then, right?
Re: ?? tough update !! [message #19572 is a reply to message #19564] Thu, 28 March 2002 13:44 Go to previous messageGo to next message
DRE
Messages: 9
Registered: March 2002
Junior Member
Its 8i. Heres the sql+ login message:
Oracle8i Enterprise Edition Release 8.1.6.0.0.
DRE
Re: ?? tough update !! [message #19574 is a reply to message #19572] Thu, 28 March 2002 15:43 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Here's a sample script under 8.1.7. Does this work the same way for you?

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
With the Partitioning option
JServer Release 8.1.7.1.0 - Production
 
sql>create table t as select * from all_objects;
 
Table created.
 
sql>select count(*) from t;
 
 COUNT(*)
---------
     3915
 
sql>update t   
  2     set subobject_name = 'random update' 
  3   where rowid in 
  4     (select rowid                   
  5        from (select rowid                
  6                from t                    
  7               order by dbms_random.value)
  8       where rownum <= 50);
 
50 rows updated.
 
sql>select count(*) from t where subobject_name is not null;
 
 COUNT(*)
---------
       50
Re: ?? tough update !! [message #19581 is a reply to message #19572] Fri, 29 March 2002 05:37 Go to previous messageGo to next message
DRE
Messages: 9
Registered: March 2002
Junior Member
Hi Todd, that worked! I apologize for not reading your previous post as closely as I should have as I missed the 2nd nested query(duhh). Good thinking! Either way, thanks a lot!! Have a great day!
Re:easy method using mod function [message #19588 is a reply to message #19564] Fri, 29 March 2002 19:04 Go to previous message
P.Deshmukh
Messages: 17
Registered: March 2002
Junior Member
Hi,
I hope this will work,
=============================================
update t1 set
somefield=decode(mod(rownum,2),0,'M','F');
============================================
Previous Topic: update does not work..
Next Topic: Re: Storing a number with exactly 10 decimal places
Goto Forum:
  


Current Time: Fri Apr 26 02:08:19 CDT 2024