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

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 3 Mar 2006 17:32:27 -0500
Message-ID: <L8qdnbL_w-FgXpXZnZ2dnUVZ_vidnZ2d@comcast.com>

"Harry331" <harryooopotter_at_hotmail.com> wrote in message news:1141423068.302113.240200_at_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
:

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 Received on Fri Mar 03 2006 - 16:32:27 CST