Two tables:
T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 is
the primary key.
T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factor
numeric(18,2))--x1 is the primary key. T2 contains non-overlapping
values. So for eg., a few rows in T2 may look like.
1, 51, 51.999, 51
2, 52, 52.999, 52
....
....
32, 82, 82.999, 82
....
....
T2 is basically a lookup table. There is no relationship between the
two tables T1 and T2. However, if the TestVal from T1 falls in the
range between FromVal and ToVal in T2, then I want to update
ResultFactor in T1 with the corresponding value of Factor from the T2
table.
------Example for illustration only---------------
Even though tables cannot be joined using keys, the above problem is a
very common one in our everyday life. For example T1 could be
employees PayRaise table, c1=EmployeeID, with "TestVal" representing
test scores (from 1 to 100). T2 representing lookup of the ranges,
with "Factor" representing percent raise to be given to the employee.
If TestVal is 65 (employee scored 65% in a test), and a row in T2
(FromVal=60, ToVal=70, Factor=12), then I would like to update 12 in
table T1 from T2 using sql;. Basically T2 (like a global table)
applies to all the employees, so EmpID cannot serve as a key in T2.
---------------------------------------------------------
Could anyone suggest how I would solve MY PROBLEM using sql? I would
like to avoid cursors and loops.
Reply appreciated.
Thanks