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


"Harry331" <harryooopotter@xxxxxxxxxxx> wrote in message
news:1141423068.302113.240200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
:
: 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
:

you got rid of the subqueries -- looking closer you actually had 3, but the
outer one was the likely culprit

your previous post showed:

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 ...

which includes 3 subqueries that select from dual ... perhaps in-line view
is the term your more familiar with

in your new query there is no need to loop, simply getting rid of the
subqueries is all that is necessary so that dbms_random gets evaluated for
each row updated

there is also no need for a subquery to get the ids from the same table that
your updating.

you also should avoid using a function on the column that your predicate
references unless its absolutely unavoidable, in which case make sure you
use a function-based-index on that column (assuming an index is appropriate)

this is all you need:


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_exam_date_time >= date '2006-01-01'
and s.my_exam_date_time < date '2006-03-01'


so, compary this with your code and figure out what parts of your code were
unnecessary, then go buy tom kyte's book and study it before you do much
more SQL programming... you're getting it to work, but you'll do better if
you get a better feel for how the language is best designed to work

++ mcs


.

Relevant Pages

  • 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)
  • Re: update a date field with fixed date but random hours/minutes
    ... Mark C. Stock wrote: ... that's probably because you selected your random values once in the subquery ... 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 ...
    (comp.databases.oracle.misc)