Update using between - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Update using between

Author

Topic

Rudys
Starting Member

8 Posts

Posted - 2011-09-21 : 05:39:33

Hai All,

Please help me to solved this problem:
first table
col1 col2 col3
1 1 5
2 6 10
3 11 12

Second table
col1 col2
5 1
3 1
7 2
12 3

explanation:
I want to update a second table col2 with values from first table
col1 based from between values col2 and col3 in first table.

please help me, tq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 05:53:10

sorry that explanation doesnt make sense. you dont have any column in table2 to compare against col2 and col3 in table1.
At least specify what should table look like after update and whats the logic for update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rudys
Starting Member

8 Posts

Posted - 2011-09-22 : 00:53:23

here the detail explanation:

Table_A
Col_Id _ Col_from _ Col_to
1 ______ 1 __________5
2 ______ 6 __________10
3 ______ 11__________12

Table_B
Col_ID _ Col_Result
5______ 1
7______ 2
12______ 3

Row 1
Col_Result(Table_B) value '1' is from col_id(Table_A) because Co_id(Table_B) value '5'
is between col_from(Table_A) and col_to(Table_A)

Row 3
Col_Result(Table_B) value '3' is from col_id(Table_A) because Co_id(Table_B) value '12'
is between col_from(Table_A) and col_to(Table_A)

hope this explanation is clear, Please help me tq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 00:57:23

[code]
UPDATE b
SET b.Col_Result=a.Col_ID
FROM Table_B b
INNER JOIN TableA a
ON b.Col_ID >= a.Col_from
AND b.Col_ID <= a.Col_to
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rudys
Starting Member

8 Posts

Posted - 2011-09-22 : 06:10:32

solved my probelm. Thank you all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 11:43:42

quote:
Originally posted by Rudys

solved my probelm. Thank you all.

you regarded me as all

anyways welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources