1

Okay, first I identify the date integer for the particular date, onwards from which i need to update the random values into the dpm_dateto column :

Query :

select to_char(to_date('15/05/2013','dd/mm/yyyy'), 'J') from dual;

Result:

2456428

Now, I try to update the dpm_dateto column using the query below:

update t_dailypm
set dpm_dateto = 
     (select to_date(trunc(dbms_random.value(2456428,2456428+76)), 'J') from dual)
where dpm_loc = 'P2' and dpm_department like '%IN%';

Result:

900 rows updated.

But, the problem is that each distinct value in dpm_dateto column is updated with the same date. I can't get my head around this. Kindly help.

unlimit
  • 3,672
  • 2
  • 26
  • 34
user1728293
  • 11
  • 2

1 Answers1

4

Don't use a subquery.
Just set=your_expression, like in the below example.
Oracle optimises the subquery and evaluates it only once, but the expression will be evaluated for each row:

create table xyz(
  abc date
);

insert into xyz
select sysdate from dual
connect by level < 6;

select * from xyz;

ABC    
--------
13/07/17 
13/07/17 
13/07/17 
13/07/17 
13/07/17

and now:

update xyz set abc = to_date(trunc(dbms_random.value(2456428,2456428+76)), 'J');

select * from xyz;

ABC    
--------
13/06/14 
13/07/23 
13/07/26 
13/06/24 
13/07/10
krokodilko
  • 35,300
  • 7
  • 55
  • 79