October 13, 2010 at 12:06 am
I have two tables.
Table 1 has 3 columns with values in only 2 columns:
from tocode
23
34
46
68
Table 2 has 3 columns with values:
from tocode
110A
How do I write a script to assign code 'A' in Table 2 to Table 1 on condition that the 'from' OR 'to' values in Table 1 fall within 'from' and 'to' values in Table 2
October 13, 2010 at 12:21 am
you can try something like this
update Table1
set Code = (select t2.code
from Table2 't2'
where t1.From <= t2.From
and t1.To >= t2.To)
from Table1 't1'
October 13, 2010 at 12:09 pm
Thanks for now.
what do t1 and t2 in your code represent? is that the same as names for table1 and table2?
October 13, 2010 at 12:25 pm
Yes they are alias names for the tables.
October 13, 2010 at 12:57 pm
Ok.
here is your script:
update Table1
set Code = (select t2.code
from Table2 't2'
where t1.From <= t2.From
and t1.To >= t2.To)
from Table1 't1'
assuming the real names of the tables in SQL are:
dbo.table1
dbo.table2
should the script be written as:
update dbo.table1
set Code = (select dbo.table2
.code
from dbo.table2
where dbo.table1
.From <= dbo.table2
.From
and dbo.table1
.To >= dbo.table2
.To)
from dbo.table1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply