Re: update a date field with fixed date but random hours/minutes

From: Harry331 <harryooopotter_at_hotmail.com>
Date: 3 Mar 2006 13:57:48 -0800
Message-ID: <1141423068.302113.240200@e56g2000cwe.googlegroups.com>

Mark C. Stock wrote:
> "Harry331" <harryooopotter_at_hotmail.com> wrote in message
[snip]
> : But the result was that all 3 records have the same hour/minutes
> : values.
> :
>
> that's probably because you selected your random values once in the subquery
>
> get rid of the subquery -- select from dual is only necessary when your
> evaluating an expression outside of the SQL statement that you're going to
> implemetn it within

Thanks a lot, Mark.

Not sure what you mean by getting rid of the subquery.

Here is a revision (which I use a loop) that works (but I think it's not efficient):
Any suggestion how I can improve it?

begin
  for i in 1..10 loop
    update study s

      set s.my_exam_date_time =
      to_date(to_char(sysdate -1,'YYYY-MM-DD')
                  ||' '
                  ||trunc(dbms_random.value(0,24))
                  ||':'
                  ||trunc(dbms_random.value(0,60)),
              'YYYY-MM-DD hh24:mi')
      where
        s.my_study_id in
      (
      select
        s.my_study_id as Study_ID
      from
        study s
      where
        to_char(s.my_exam_date_time, 'YYYY-MM-DD') >= '2006-01-01'
        and to_char(s.my_exam_date_time, 'YYYY-MM-DD') <= '2006-02-28'
        and rownum=1
      );

  end loop;
end;
/

select
  s.my_study_id as Study_ID,
  to_char(s.my_exam_date_time, 'YYYY-MM-DD hh24:mi') as exam_date from
  study s
where
  s.my_study_id in ('1000001', '1000011', '1000012');

Study_ID Exam_Date

--------  ---------
1000001	2006-03-02 03:02
1000011	2006-03-02 19:56
1000012	2006-03-02 04:29

Received on Fri Mar 03 2006 - 15:57:48 CST