I have two tables like the following:
TABLE1:
=======
somid, tobeupdated
1 , null
2 , null
3 , null
10 , null
TABLE2:
=======
rangeofids
2
3
9
10
11
12
13
I have to update TABLE1.tobeupdated (or found its' should be value) based on the following criteria(s):
TABLE1.somid NOT exists in TABLE2.rangeofids
, then the expected result is: tobeupdated = TABLE1.somid
TABLE2.rangeofids
which is larger then TABLE1.somid
So the expected values are:bu
TABLE1:
=======
somid, tobeupdated
1 , 1
2 , 4
3 , 4
10 , 14
I tried hard, but the simplest solution I came up with is creating a temporary table with a full sequence of ids (from 1
to max(rangeofids)+1
) MINUS TABLE2.rangeofids
so I can found the MIN(TMPTABLE.id) where TMPTABLE.ID > TABLE1.somid
.
But isn't there a better solution (without the temp table)?
Note: I can't create procedures/functions, etc, so it must be standard (Oracle 10) SQL.
This is my try.
First we should decide using only table2 what value should return after finding the value there.
select rangeofids,
candidate,
nvl(candidate,lead(candidate ignore nulls) over (order by rangeofids)) as full_candidate
from (
select rangeofids, case when dist=1 then null else rangeofids+1 end as candidate
from (
select rangeofids,
lead(rangeofids) over (order by rangeofids) - rangeofids as dist
from table2
)
);
After this a merge into table1 with
the below select will solve the problem:
select someid, nvl(full_candidate, someid)
from table1 a
left join (
--the above query
) b
on a.someid = b.rangeofids;