2 Replies Latest reply on Mar 24, 2013 2:16 AM by TobiP

    update clomun with random ids from other table

      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,
        • 1. Re: update clomun with random ids from other table
          Iordan Iotzov
          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
            p IN NUMBER  
          ) RETURN VARCHAR2 AS 
          r number;
              with rnd
              (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

          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
          1 person found this helpful
          • 2. Re: update clomun with random ids from other table
            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
                a number;
                 select speid into a
                   from ( select  rownum rw, speid
                          from somepersons  
                   where rw = (select floor( dbms_random.value (1,951)) from dual);
                 return a;
            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. :)