declare @tb1 table(num1 char(9),name1 varchar(40)) declare @tb2 table(num2 char(6),name2 varchar(40))insert into @tb1 values('000001001','') insert into @tb1 values('000002002','') insert into @tb1 values('000003003','')insert into @tb2 values('000001','上海') insert into @tb2 values('000002','北京') insert into @tb2 values('000003','广州')update @tb1 set name1=(select top 1 name2 from @tb2 where left(num1,6)=num2)select * from @tb1
update A set 所在区县=B.所在区县 from A,B where A.substring(税号,1,6) =B.所在区县)
declare @tb1 table(num1 char(9),name1 varchar(40)) --借用测试数据。 declare @tb2 table(num2 char(6),name2 varchar(40))insert into @tb1 values('000001001','') insert into @tb1 values('000002002','') insert into @tb1 values('000003003','')insert into @tb2 values('000001','上海') insert into @tb2 values('000002','北京') insert into @tb2 values('000003','广州')--no1 update @tb1 set name1=(select max( name2) from @tb2 where left(num1,6)=num2) --no2 update @tb1 set name1 = b.name2 from @tb1 a join @tb2 b on left(a.num1,6) = num2 select * from @tb1
update A set A.所在县区=B.所在县区 from A,B where left(A.税号,6)=B.行政编码
update A set 所在区县=b.所在区县 from B where left(税号,6)=B.行政编码
update a set a.所在区县=b.所在区县 from a,b where left(a.税号,6)=B.行政编码,这样就ok了,试试看吧,其实这是简单的sql,楼主要好好学习啦。
update a set a.所在区县=(select top 1 所在区县 from b where left(a.所在区县1,6)=b.所在区县)
update a set a.所在区县=(select top 1 所在区县 from b where left(a.税号1,6)=b.行政编码)
操作符丢失在'B.所在区县 from A'中,怎么回事啊,虽然还是不能运行,不过有你们的帮助,我懂了不少,谢谢
set A.所在区县=B.所在区县
from A inner join
B on left(A.税号,6)=B.行政编码
declare @tb2 table(num2 char(6),name2 varchar(40))insert into @tb1 values('000001001','')
insert into @tb1 values('000002002','')
insert into @tb1 values('000003003','')insert into @tb2 values('000001','上海')
insert into @tb2 values('000002','北京')
insert into @tb2 values('000003','广州')update @tb1 set name1=(select top 1 name2 from @tb2 where left(num1,6)=num2)select * from @tb1
=B.所在区县)
--借用测试数据。
declare @tb2 table(num2 char(6),name2 varchar(40))insert into @tb1 values('000001001','')
insert into @tb1 values('000002002','')
insert into @tb1 values('000003003','')insert into @tb2 values('000001','上海')
insert into @tb2 values('000002','北京')
insert into @tb2 values('000003','广州')--no1
update @tb1 set name1=(select max( name2) from @tb2 where left(num1,6)=num2)
--no2
update @tb1 set name1 = b.name2 from @tb1 a join @tb2 b on left(a.num1,6) = num2
select * from @tb1