34,588
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([姓名] varchar(4),[邮编] int,[区域] varchar(8))
insert [tb]
select '何光',null,null union all
select '刘月',201600,null union all
select '褚晓',200135,'浦东新区' union all
select '任广',200040,null union all
select '刘静',200020,null union all
select '都锡',null,null union all
select '周建',201112,'闵行'
--> 测试数据:[postcode]
if object_id('[postcode]') is not null drop table [postcode]
create table [postcode]([区域] varchar(6),[邮编] int)
insert [postcode]
select '卢湾',200020 union all
select '徐汇',200030 union all
select '长宁',200050 union all
select '静安',200040 union all
select '普陀',200333 union all
select '闸北',200070 union all
select '虹口',200080 union all
select '杨浦',200082 union all
select '闵行',201100 union all
select '宝山',201900 union all
select '嘉定',201800 union all
select '浦东新',200135 union all
select '金山',200540 union all
select '松江',201600 union all
select '青浦',201700 union all
select '南汇',201300 union all
select '奉贤',201400 union all
select '崇明',202150 union all
select '黄浦',200001
update t
set [区域]=r.[区域]
from [tb] t,[postcode] r
where t.[邮编]=r.[邮编]
select * from [tb]
----------------------
何光 NULL NULL
刘月 201600 松江
褚晓 200135 浦东新
任广 200040 静安
刘静 200020 卢湾
都锡 NULL NULL
周建 201112 闵行
update t set [区域]=r.[区域] from [tb] t,[postcode] r
where t.[邮编]=r.[邮编]