求sql语句

[ccz500]

表如下：
id sno cno add
1 001 013 a
2 002 023 b
3 002 024 b
4 003 033 c
5 001 013b
6 002 023b
7 003 033b
想把所有cno包含b的行的add值修改为同一sno 且和cno前三位相等的行的add值。谢谢请指教

[ccz500]

比如修改为
id sno cno add
1 001 013 a
2 002 023 b
3 002 024 b
4 003 033 c
5 001 013b a
6 002 023b b
7 003 033b c

[Alang_79]

update table a
set a.add = (select top 1 add from table where sno = a.sno and cno = left(a.cno ,3))

[zoubsky]

declare @tb1 table(id int identity(1,1), sno char(10), cno char(10),add1 char(10))
insert @tb1
select '001','013','a' union all
select '002','023','b' union all
select '002','024','b' union all
select '003','033','c' union all
select '001','013b',null union all
select '002','023b',null union all
select '003','033b',null

update @tb1 set add1 = b.add1 from @tb1 as a inner join (select * from @tb1 where cno not like '%b%')b on a.sno = b.sno where a.cno like '%b%'

select * from @tb1
/*
测试结果
id sno cno add1
----------- ---------- ---------- ----------
1 001 013 a
2 002 023 b
3 002 024 b
4 003 033 c
5 001 013b a
6 002 023b b
7 003 033b c
*/

[mislrb]

create table #t([id] int identity(1,1),sno varchar(3),cno varchar(10),[add] varchar(1))
insert #t
select '001','013','a' union all
select '002','023','b' union all
select '002','024','b' union all
select '003','033','c' union all
select '001','013b',null union all
select '002','023b',null union all
select '003','033b',null

update #t
set [add]=(select a.[add] from #t a where a.cno=substring(#t.cno,1,3) and a.sno=#t.sno)
where substring(#t.cno,len(#t.cno),1)='b'

drop table #t