CODEWITHSUNDEEP
oracle-database
JavaSheriff
JavaSheriff

Reputation: 7665

updating random string value to existing records in an oracle table

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;

Upvotes: 2

Views: 69

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

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

Upvotes: 3

Related Questions