Re: Simple update problem

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 22 May 2005 01:05:12 +0200
Message-ID: <428FBEA8.7090008@skynet.be>

b_addams_at_yahoo.com wrote:
> Hello. I have a table that stores readings from temperature sensors
> every 15 minutes. My problem is that one of them had a problem and kept
> reading the same temperature for a couple of days. Luckily (and
> surprisingly), this particular one is one of the few that had a backup
> sensor. So, what I am trying to do is update the problem sensor's
> readings with the backup sensor's readings for an approximately two day
> period. I am having trouble with the syntax on the update. Can anyone
> help?
>
> Here is the table info:
>
> SQL> describe temperature_log
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> SYSTEM_TIMESTAMP NOT NULL DATE
> SENSOR_ADDRESS NOT NULL VARCHAR2(4)
> DEGREES_F NOT NULL NUMBER(38)
> DEGREES_C NOT NULL VARCHAR2(8)
>
> I am trying to put the two temp fields from the rows with an 'FFFF'
> address into the rows with a '6DA9' address for a period of time.
>
> Thanks.
> Bill
>

something like this?

update temperature_log tl_bad

   set (degrees_f, degrees_c) =
    (select tl_good.degrees_f, tl_good.degrees_c

       from temperature_log tl_good
       where tl_good.system_timestamp = tl_bad.system_timestamp
         and tl_good.sensor_address   = 'FFFF')

where tl_bad.sensor_address = '6DA9'

   and tl_bad.system_timestamp

       between to_date('&dattim_from','YYYYMMDDHH24MISS')
           and to_date('&dattim_till','YYYYMMDDHH24MISS')

Received on Sat May 21 2005 - 18:05:12 CDT