create table t1(id int,status varchar(04))
insert into t1 select 1,'0011'
insert into t1 select 2,'0011'
create table t2(id int,s1 int, s2 int,s3 int,s4 int)
insert into t2 select 1,1,3,6,9
insert into t2 select 2,6,9,1,3
declare @s int
set @s=2
update t1
set status=case when @s between s1 and s2 then stuff(status,3,1,'0')
when @s between s3 and s4 then stuff(status,4,1,'0')
else status end
from t1 ,t2
where t1.id=t2.id
select * from t1
/*
id status
----------- ------
1 0001
2 0010
*/
Create Table table1
(id Int,
status Char(4))
Insert table1 Select 1, '0011'
Union All Select 2, '0011'
Create Table table2
(id Int,
s1 Int,
s2 Int,
s3 Int,
s4 Int)
Insert table2 Select 1, 1, 3, 6, 9
GO
Declare @I Int
Select @I = 2
Update
A
Set
status = (Case When @I Between s1 And s2 Then Stuff(status, 3, 1, '0')
When @I Between s3 And s4 Then Stuff(status, 4, 1, '0')
Else status End)
From
table1 A
Inner Join
table2 B
On A.ID = B.ID
Select * From table1
GO
Drop Table table1, table2
--Result
/*
ID status
1 0001
2 0011
*/
update a
set
status=case
when a.id between b.s1 and b.s2 then stuff(a.status,3,1,'0')
when a.id between b.s3 and b.s4 then stuff(a.status,4,1,'0')
else a.status
end
from
table1 a,table2 b
Declare @I Int
Select @I = 5
Update
A
Set
status = (Case When @I Between s1 And s2 Then Stuff(status, 3, 1, '0')
When @I Between s3 And s4 Then Stuff(status, 4, 1, '0')
Else status End)
From
table1 A
Inner Join
table2 B
On A.ID = B.ID
Create Table table1
(id Int,
status Char(4))
Insert table1 Select 1, '0011'
Union All Select 2, '0011'
Create Table table2
(id Int,
s1 Int,
s2 Int,
s3 Int,
s4 Int)
Insert table2 Select 1, 1, 7, 5, 9
GO
Declare @I Int
Select @I = 6
Update
A
Set
status = (Case When @I Between s1 And s2 And @I Between s3 And s4 Then Stuff(Stuff(status, 3, 1, '0'), 4 ,1, '0')
When @I Between s1 And s2 Then Stuff(status, 3, 1, '0')
When @I Between s3 And s4 Then Stuff(status, 4, 1, '0')
Else status End)
From
table1 A
Inner Join
table2 B
On A.ID = B.ID
Select * From table1
GO
Drop Table table1, table2
--Result
/*
ID status
1 0000
2 0011
*/