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

b_addams (AT) yahoo (DOT) com wrote in
news:1116713893.977189.42480 (AT) f14g2000cwb (DOT) googlegroups.com:

Your problem statement & terminology leave a lot to be desired, IMO.
I speculate that you do NOT really want to UPDATE.
I suspect you should DELETE the bad rows and
INSERT rows from the backup sensor.

Or can & do you guarentee that during the period of interest,
a one to one match in timestamps occur for both sensors?

b_addams (AT) yahoo (DOT) com wrote:
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')

Thanks for the input. I think I do really want to update, as I checked
the logs and there was a one to one match for the time period. I guess
it doesn't really matter whether I insert or update as the end result
is the same.

I am trying to learn more SQL. Although I have been using it for a
while, it's been a small part of the coding I have been doing and
generally very simple inserts, updates and deletes (usually only one or
two tables in a query). Time to actually sit down and try more complex
stuff has been a problem. I do appreciate the input as I don't want to
be one of those who put in bad problem statements and are too lazy to
do any research, etc.

Thanks.
Bill

Thank you! That definitely helps. I did just think of another
possibility though. Maybe a smarter way to do this would be a test for
any set where the temperature difference is greater than a certain
value. With the update above I am not sure how to do this as the final
where clause is outside the 'tl_good' select clause so obviously I
can't use the 'tl_good.degrees_f' in the test. Is there an easy way to
do this?

Bill

b_addams (AT) yahoo (DOT) com wrote:
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, tl_bad.system_timestamp)
in (select g.sensor_address, g.system_timestamp
from temperature_log g,
temperature_log b
where g.sensor_address = '6DA9'
and g.system_timestamp between to_date('&dtfrom','YYYYMMDDHH24MISS')
and to_date('&dttill','YYYYMMDDHH24MISS')
and b.sensor_address = 'FFFF'
and b.system_timestamp = g.system_timestamp
and b.degrees_f != g.degrees_f /* or something more complex */
)