Need help with reference tables - subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with reference tables - subquery

Author

Topic

cvarick
Starting Member

2 Posts

Posted - 2008-06-18 : 17:32:58

Ok so I am trying to update rows which have a rNumber in the range of a specific lookup table fields. Example.

Table A (source table)
----------
cn, rNumber
null, 223
null, 9999
null, 12345

Table B (Reference table)
-------------
cn, start_r, end_r
A, 1, 300
B, 9000, 10000
C, 11000, 13000

I want to find the 'cn' for tableA.rNumber where it exists in the range of tableB.start_r and tableB.end_r
how do I do it??

-Chris Varick

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-06-18 : 18:00:09

Someting like this:

SELECT B.cn
FROM TableB AS B
INNER JOIN TableA AS A
ON A.rNumber BETWEEN B.start_r AND B.end_r

cvarick
Starting Member

2 Posts

Posted - 2008-06-18 : 18:35:51

quote:
Originally posted by Lamprey

Someting like this:
SELECT B.cn
FROM TableB AS B
INNER JOIN TableA AS A
ON A.rNumber BETWEEN B.start_r AND B.end_r

Thanks! for the help Lamprey.. Ended up doing something very similar.
Now I am curious how I would update TABLEA.cn with TABLEB.cn after finding the matching rage :)

-Chris Varick

singularity
Posting Yak Master

153 Posts

Posted - 2008-06-18 : 19:43:36

UPDATE A
SET A.cn = B.cn
FROM TableA AS A
INNER JOIN TableB AS B
ON A.rNumber BETWEEN B.start_r AND B.end_r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources