SQL Server 两表字段对比后UPDATE符合条件的字段

qqlove926

库中有2个表,A表和B表
A表中字段如下
ID AREA                  CITYID
1  北京朝阳区             NULL
2. 广东省深圳市南山区1    NULL
3. 广东省深圳市福田区     NULL
4. 广东省深圳市南山区2    NULL
5. 广东省                 NULL
B表字段
ID  CITY                 SZCODE
1.  北京市               110000
2.  朝阳区               110105
3.  广东省               440000
4.  深圳市               440300
5.  南山区               440305
现在想将A表里的CITYID 用B表里的SZCODE填充
根据A表的AREA和B表的CITY对比后进行插入CITYID
A表数据大约30万数据,而B表就是中国行政区域划分数据,大约3500数据
A表替换后的效果如下:
ID AREA                  CITYID
1  北京朝阳区             NULL(110105)
2. 广东省深圳市南山区1    NULL(440305)
3. 广东省深圳市福田区     NULL(440303)
4. 广东省深圳市南山区2    NULL(440305)
5. 广东省                 NULL(440000)
刚学SQL,请大家帮忙.....

lj0917

update A set a.CITYID=b.SZCODE from A a,B b where a.ID=b.ID

or

update A set a.CITYID=b.SZCODE from A a inner join B b on a.ID=b.ID

fanfan1980

--try

update A set CITYID=(select max(B.SZCODE) as SZCODE from B where charindex(B.CITY, A.AREA)>0)

fanfan1980

update A set CITYID=B.SZCODE
from B
where charindex(B.CITY, A.AREA)>0

kxiaper

update A set a.CITYID=b.SZCODE
from A a
inner join B b on a.ID=b.ID and charindex(B.CITY, A.AREA)>0

kxiaper

--更正：
update A set a.CITYID=b.SZCODE
from A a, B b
where charindex(B.CITY, A.AREA)>0

prajna

select a.*,b.SZCODE,charindex(CITY,AREA) as a into # from a,b where charindex(CITY,AREA)>0

update a set CITYID= SZCODE from # t where a.AREA=t.AREA and not exists(select * from # where id=t.id and a>t.a)

drop table #

select * from a

--result

1        北京朝阳区        110105
2        广东省深圳市南山区1        440305
3        广东省深圳市福田区        440300
4        广东省深圳市南山区2        440305
5        广东省        440000


楼主要求的是最小级匹配

kxiaper

楼上正解，当where charindex(B.CITY, A.AREA)>0时会有多个匹配，取最大的那个
因为最大值表示区域靠后

qqlove926

恩 是最小级别的匹配,然后依次上升
如:广东省深圳市南山区 如果可以查到有这个"南山区"匹配的就按照南山区的来进行操作
如果B表里无南山区,那么按照深圳市操作.
A表里的AREA字段中含有南山区的很多记录
其实就是将IP库里的信息同行政区进行匹配,这样可以进行实际项目操作
(根据IP自动选择城市,区).
如果直接用IP库里(A表)的信息的话,不是很规范,所以需要和行政区数据表(B表)进行匹配后使用.

qqlove926

A表==ip  B表==City
字段 AREA===F_ip_area  CITYID==F_city_id   SZCODE==sz_code CITY==city
是否转换为下面的?
哎 大家别笑话俺,刚接触.如果转换成功,我把数据库拿出来,大家用着也方便,看到很多人都需要这样的功能;IP--IP对应区域---行政区(较IP显示的区域更规范,便于实际应用)
select ip.*,City.sz_code,charindex(city,F_ip_area) as ip into # from ip,City where charindex(city,F_ip_area)>0

update ip set F_city_id= sz_code from # t where ip.F_ip_area=t.F_ip_area and not exists(select * from # where id=t.id and ip>t.ip)

drop table #

select * from ip