Hi all,

I need to update a column depending on how many hours since the last data load, giving a new value depending on the result. Basically the values are from 1-12, for hours starting from 0, i.e. if the time difference between the contacttime (a current column) and the current time is between 0-1 hours then the value should be 1, 2-3 should be 2, 4-5 should be 3, and so on.

After some help on these boards my statement picks out the correct values:

select
CASE
( case
when (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) < 0 )
then (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) + 24)
else (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)))
end )
when 0 then 1
when 1 then 1
when 2 then 2
when 3 then 2
when 4 then 3
when 5 then 3
when 6 then 4
when 7 then 4
when 8 then 5
when 9 then 5
when 10 then 6
when 11 then 6
when 12 then 7
when 13 then 7
when 14 then 8
when 15 then 8
when 16 then 9
when 17 then 9
when 18 then 10
when 19 then 10
when 20 then 11
when 21 then 11
ELSE 12
END new_value
from scho.wcontactlogging;

My question is, how would I then translate that to an update statement? Any help/suggestions much appreciated.