updating random string value to existing records in an oracle table

Question

I would like to update existing records in an oracle table with a random string (YYYY-DD)
I have successfully created a query that returns a random date value like so:

select  to_char(TO_DATE('2003/07/09', 'yyyy/mm/dd') + trunc(dbms_random.value(0,(TO_DATE('2003/07/09', 'yyyy/mm/dd')-TO_DATE('2018/07/09', 'yyyy/mm/dd')+1))),
                         'YYYY-MM') rDate   from dual

But for some reason my update script is setting the same value to all the records why is it not random?

CREATE TABLE tmp_del_me (    
    name varchar(255),
    year_month_started varchar(255)
);

insert into tmp_del_me (name)VALUES ('a');
insert into tmp_del_me (name)VALUES ('b');
insert into tmp_del_me (name)VALUES ('c');
insert into tmp_del_me (name)VALUES ('d');


UPDATE tmp_del_me
SET  year_month_started = (
                    select  to_char(TO_DATE('2003/07/09', 'yyyy/mm/dd') + trunc(dbms_random.value(0,(TO_DATE('2003/07/09', 'yyyy/mm/dd')-TO_DATE('2018/07/09', 'yyyy/mm/dd')+1))),
                     'YYYY-MM') rDate   from dual
                     )
            /

drop table tmp_del_me;

Derviş Kayımbaşıoğlu · Accepted Answer · 2018-12-20 22:27:19Z

3

Good Catch!!

Your inner query executes only once and always same value is assigned to year_month_started column.

If you don't use subquery, then the DML works as expected.

UPDATE T1
SET COL1 = 
  to_char(TO_DATE('2003/07/09', 'yyyy/mm/dd') + 
     trunc(dbms_random.value(0,(TO_DATE('2003/07/09', 'yyyy/mm/dd')-
     TO_DATE('2018/07/09', 'yyyy/mm/dd')+1))), 'YYYY-MM') ;

Here is the fiddler link: http://sqlfiddle.com/#!4/43bb3f

answered Dec 20, 2018 at 22:27

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JavaSheriff Over a year ago

working great, I still don't understand what is the difference.

Collectives™ on Stack Overflow

updating random string value to existing records in an oracle table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related