This discussion is archived
2 Replies Latest reply: Mar 23, 2013 7:16 PM by TobiP RSS

update clomun with random ids from other table

TobiP Explorer
Currently Being Moderated
Hi guys!

For test-data I am trying to update a column with random ids (<i>SPEID</i>) from another table. So they should not be just e.g. 1 to 1000, but correspond to the other values.

I don't quite understand the result I am getting, although it should be really simple, and hope you can help me.

My query so far:
update morevalues set mvspeid = (

   select max(speid) from  
     ( select rownum rw, speid from somepersons )
   where rw = floor (dbms_random.value (1,951)) 
);
(Table<i> somepersons</i> contains 950 records.)

What confuses me, is that that inner query sometimes return 2 result columns, sometimes null, although to my understanding it sould always match just one SPEID. (Therefore the <i>max()</i> )

Second, that inner query seems to get executed not only once for all, not once for each record in the <i>morevalues</i>-table.

Thanks for any advice,
regards
tobi
  • 1. Re: update clomun with random ids from other table
    Iordan Iotzov Expert
    Currently Being Moderated
    The situation looks quite unusual indeed. These days Oracle does quite a bit of internal transformations and optimizations, so a trace might give you a starting point for an investigation.
    There is an easy work around though.
    Create a function
    CREATE OR REPLACE FUNCTION EVERY_TIME 
    (
      p IN NUMBER  
    ) RETURN VARCHAR2 AS 
    r number;
    BEGIN
        with rnd
        as
        (select dbms_random.value (1,951) r from dual) 
        select ln 
        into r
        from ( select  line ln , rownum rw
        from somepersons  ) 
        where rw = (select floor (r) from rnd);
    and then simply issue
    update morevalues set mvspeid = every_time(mvspeid)
    Iordan Iotzov
    http://iiotzov.wordpress.com/


    P.S. I have not tried this myself…

    Edited by: Iordan Iotzov on Mar 22, 2013 4:00 PM

    Edited by: Iordan Iotzov on Mar 22, 2013 4:01 PM
  • 2. Re: update clomun with random ids from other table
    TobiP Explorer
    Currently Being Moderated
    Hi Iordan,

    thanks for your help!

    Its wierd thou that this seems not to be solvable simpler than with plsql.

    Getting into a procedural solution, I also tried going with a for-loop with a select on the table and an update for each row. Worked, but was a bit too complicated. I like your solution with a function better. The query can be simplified a bit thou:
    create or replace function getRndSPEID
    return NUMBER
    is
        a number;
      begin
         select speid into a
           from ( select  rownum rw, speid
                  from somepersons  
                )
           where rw = (select floor( dbms_random.value (1,951)) from dual);
         return a;
      end;
    Works fine.

    Its really strange thou, that also there, when you just put
    where rw = floor( dbms_random.value( 1, 951 ) ) ;
    you get the null or 2 result rows as I mentions in my first post.

    PS. I leave this thread open, just in case there still IS another solution just with one DML statement. :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points