Lookup value query joining two tables

David:

It is not a join as keys are not present in both the tables; between
will just restrict the values for test_val. However, the following
solution has been proposed by Joe Celko, which works. Thanks to
everybody who contibuted. David and Joe, thanks so much.

UPDATE T1
SET factor
= (SELECT T2.factor
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val)
WHERE EXISTS
(SELECT *
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val);

--CELKO--> It is not a join as keys are not present in both the tables

Keys are not a requirement for a join. Any boolean expression (including the
BETWEEN predicate) can be used as the basis of a join between two tables.
That's with or without keys or corresponding columns in both tables.

--
David Portas
SQL Server MVP
--