2 Replies Latest reply: Mar 23, 2013 9:16 PM by TobiP RSS

    update clomun with random ids from other table

    TobiP
      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
          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
            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. :)