Sql-Server
SQL Server :: 如何將國家程式碼列移動到另一個表?
我有 2 張桌子:
tbl_Countries
:250 個值,包括CountryName
(Afghanistan)、TwoCharCountryCode
(AF)、ThreeCharCountryCode
(AFG) 等列。像這樣:
CountryName TwoCharCountryCode ThreeCharCountryCode Afghanistan AF AFG Aland Islands AX ALA Albania AL ALB Algeria DZ DZA American Samoa AS ASM Andorra AD AND Angola AO AGO
和
population_by_country_2020
: 235 個值,其中包含Country_or_dependency
(Afghanistan) 列,但缺少 2 和 3CHAR
國家程式碼。像這樣:
CountryName TwoCharCountryCode ThreeCharCountryCode Afghanistan NULL NULL Albania NULL NULL Algeria NULL NULL American Samoa NULL NULL Andorra NULL NULL Angola NULL NULL Anguilla NULL NULL
……是的,因為他們有不同的價值觀,他們永遠不會完美匹配,但我可以用手做那些錯過的人。
我總體上想做的是
AF
在AFG
“阿富汗”行中來回tbl_Countries
移動。population_by_country_2020
如何存檔?
這是你想要的?
--demo setup drop table if exists tbl_Countries; drop table if exists population_by_country_2020; go CREATE TABLE tbl_Countries ( CountryName VARCHAR(17), TwoCharCountryCode varchar(20), ThreeCharCountryCode VARCHAR(3) ); INSERT INTO tbl_Countries (CountryName, TwoCharCountryCode, ThreeCharCountryCode) VALUES ('Afghanistan', 'AF', 'AFG'), ('Aland Islands', 'AX', 'ALA'), ('Albania', 'AL', 'ALB'), ('Algeria', 'DZ', 'DZA'), ('American Samoa', 'AS', 'ASM'), ('Andorra', 'AD', 'AND'), ('Angola', 'AO', 'AGO'); CREATE TABLE population_by_country_2020 ( CountryName VARCHAR(19), TwoCharCountryCode varchar(2), ThreeCharCountryCode VARCHAR(4) ); INSERT INTO population_by_country_2020 (CountryName, TwoCharCountryCode, ThreeCharCountryCode) VALUES ('Afghanistan', NULL, NULL), ('Albania', NULL, NULL), ('Algeria', NULL, NULL), ('American Samoa', NULL, NULL), ('Andorra', NULL, NULL), ('Angola', NULL, NULL), ('Anguilla', NULL, NULL); -- the solution UPDATE p SET p.TwoCharCountryCode = c.TwoCharCountryCode ,p.ThreeCharCountryCode = c.ThreeCharCountryCode FROM population_by_country_2020 p JOIN tbl_Countries c ON c.CountryName = p.CountryName --verfy SELECT * FROM population_by_country_2020
| | | | |----------------|--------------------|----------------------| | CountryName | TwoCharCountryCode | ThreeCharCountryCode | | Afghanistan | AF | AFG | | Albania | AL | ALB | | Algeria | DZ | DZA | | American Samoa | AS | ASM | | Andorra | AD | AND | | Angola | AO | AGO | | Anguilla | NULL | NULL |