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

Do you just want to join on BETWEEN:

....
FROM T1 JOIN T2 ON T1.testval BETWEEN T2.fromval AND T2.toval

or am I misunderstanding your question?

> I want to update
> ResultFactor in T1 with the corresponding value of Factor from the T2
> table.

Why do you want to do that? Don't create redundant, derived results in your
table if you can avoid it. Do the calculation in a view or query.

--
David Portas
SQL Server MVP
--