SQL Server UPDATE eligible field after comparing two table fields

qqlove926

There are 2 tables in the library, table A and table B
The fields in table A are as follows
ID AREA CITYID
1 Beijing Chaoyang District NULL
2. Nanshan District, Shenzhen, Guangdong, China 1 NULL
3. Futian District, Shenzhen City, Guangdong Province NULL
4. Nanshan District, Shenzhen City, Guangdong Province 2 NULL
5. Guangdong Province NULL
Table B fields
ID CITY SZCODE
Beijing 110000
2. Chaoyang District 110105
3. Guangdong Province 440000
4. Shenzhen 440300
5. Nanshan District 440305
Now I want to fill the CITYID in table A with the SZCODE in table B
Insert CITYID after comparing AREA of table A and CITY of table B
The data in table A is about 300,000, and the data in table B is the administrative division of China.
The effect of table A replacement is as follows:
ID AREA CITYID
1 Beijing Chaoyang District NULL (110105)
2. Nanshan District, Shenzhen, Guangdong, China (440305)
3. Futian District, Shenzhen City, Guangdong Province NULL (440303)
4. Nanshan District, Shenzhen City, Guangdong Province 2 NULL (440305)
5. Guangdong Province NULL (440000)
Just learned SQL, please help ...

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

--correct:
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 Beijing Chaoyang District 110105
2 Nanshan District, Shenzhen, Guangdong 1 440305
3 Futian District, Shenzhen, Guangdong 440300
4 Nanshan District, Shenzhen, Guangdong 2 440305
5 Guangdong Province 440000


The landlord requires a minimum level match

kxiaper

Positive solution upstairs, there will be multiple matches when where charindex (B.CITY, A.AREA)> 0, whichever is greater
Because the maximum value indicates that the area is behind

qqlove926

En is the lowest level match and then rises in order
For example: Nanshan District, Shenzhen City, Guangdong Province
If there is no Nanshan District in the B table, then follow the Shenzhen operation.
The AREA field in table A contains many records of Nanshan District
In fact, the information in the IP library is matched with the administrative area, so that the actual project operation can be performed.
(City, district is automatically selected based on IP).
If the information in the IP library (Table A) is directly used, it is not very standardized, so it needs to be matched with the administrative district data table (Table B) and used.

qqlove926

Table A == ip Table B == City
Field AREA === F_ip_area CITYID == F_city_id SZCODE == sz_code CITY == city
Is it converted to the following?
Hey, do n’t make a joke, just contact. If the conversion is successful, I take out the database. It is convenient for everyone to use. I see that many people need such a function. (The area is more standardized, which is convenient for practical application.)
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