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


Mark C. Stock wrote:
"Harry331" <harryooopotter@xxxxxxxxxxx> 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

.

Relevant Pages

  • Re: Peoplesoft sub-query of a sub-query.
    ... How can this be re-written to eliminate the subquery? ... I can't seem to get MAX to work with the outer join on ... You can also get rid of all those levels of nested parenthesis since ...
    (comp.databases.informix)
  • Re: update a date field with fixed date but random hours/minutes
    ... :> evaluating an expression outside of the SQL statement that you're going ... 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 ... : update study s ...
    (comp.databases.oracle.misc)
  • Re: Cursor vs Set - is it possible
    ... subquery. ... number" of ABSENT entries in this table for a person, ... based on a starting date and going backwards. ... >I could simply loop a working variable with a date and ...
    (microsoft.public.sqlserver.server)
  • Re: should i use a loop or a cursor?
    ... You don't need any kind of loop... ... You can do either a subquery: ... As for computing how much was actually sold or received, ... > I have a table that stores product quantity week by week like the ...
    (microsoft.public.sqlserver.programming)
  • How to select from a table variable?
    ... I want this syntax to use to create a subquery that I can ... loop through all items in the table, ... function and/or store the items in a temporary table. ...
    (comp.databases.oracle.server)