CREATE TABLE [TABLE_A] ([vcBeg] varchar(16) ,[vcEnd] varchar(16) ,[vcArea] varchar(20))INSERT INTO TABLE_A
SELECT '1000' ,'19999','河北'
UNION ALL SELECT '2000' ,'29999','上海'
UNION ALL SELECT '3000' ,'39999','北京'
UNION ALL SELECT '4000' ,'99999','河北'
...CREATE TABLE [TABLE_B] ([vcNumber] varchar(16) ,[VCMEMO] varchar(20))INSERT INTO TABLE_A
SELECT '1002' ,'河北'
UNION ALL SELECT '1002' ,''
UNION ALL SELECT '3003' ,''
需要根据表A的条件来UPDATE表B中的VCMEMO字段,可以保证(vcBeg,vcEnd,vcNumber 全是 字符型定长数字)
SELECT '1000' ,'19999','河北'
UNION ALL SELECT '2000' ,'2999','上海'
UNION ALL SELECT '3000' ,'3999','北京'
UNION ALL SELECT '4000' ,'9999','河北' 测试数据多写一位
SELECT '1000' ,'1999','河北'
UNION ALL SELECT '2000' ,'2999','上海'
UNION ALL SELECT '3000' ,'3999','北京'
UNION ALL SELECT '4000' ,'9999','河北'
SELECT '1000' ,'1999','河北'
UNION ALL SELECT '2000' ,'2999','上海'
UNION ALL SELECT '3000' ,'3999','北京'
UNION ALL SELECT '4000' ,'9999','河北'
... CREATE TABLE [TABLE_B] ([vcNumber] varchar(16) ,[VCMEMO] varchar(20)) INSERT INTO TABLE_B
SELECT '1002' ,''
UNION ALL SELECT '1003' ,''
UNION ALL SELECT '3003' ,''
需要根据表A的条件来UPDATE表B中的VCMEMO字段,可以保证(vcBeg,vcEnd,vcNumber 全是 字符型定长数字)结果如下:
例如 1002 河北 (1999>=1002>=1000 )
1003 河北 (1999>=1003>=1000 )
3003 北京 (3000>=3003>=3999 ))
UPDATE B SET VCMEMO=(SELECT VCAREA FROM A WHERE (SELECT VCNUMBER FROM B) BETWEEN VCBEG AND VCEND)
WHERE (SELECT VCNUMBER FROM B) BETWEEN (SELECT VCBEG FROM A)AND (SELECT VCEND FROM A)
我的想法。。汗