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):
- if
TABLE1.somid NOT exists in TABLE2.rangeofids
, then the expected result is: tobeupdated =TABLE1.somid
- else find the next available (or unused)
TABLE2.rangeofids
which is larger thenTABLE1.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.
After this a
merge into table1 with
the below select will solve the problem:See SQLFIDDLE.