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

"Harry331" <harryooopotter_at_hotmail.com> wrote in message news:1141418544.155679.294300_at_u72g2000cwu.googlegroups.com...
:
: Harry331 wrote:
: [snip]
: > 4th SQL (update exam_date to be yesterday's date with random hr/mi)
: > -------
: > SQL> update study s
: > set s.my_exam_date_time =
: > to_date((select to_char(sysdate -1,'YYYY-MM-DD')
: > ||' '
: > ||(select trunc(dbms_random.value(0,24)) from dual)
: > ||':'
: > ||(select trunc(dbms_random.value(0,60)) from dual)
: > from dual),
: > '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'
: > )
: > ;
: > ... this SQL ran forever ...
: > ?
:
: I restarted the sql*plus and re-ran this SQL OK within a couple of
: seconds.
: 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

++ mcs Received on Fri Mar 03 2006 - 15:30:55 CST