Database Administrators Asked by Francesco Mantovani on January 9, 2021
I have 2 tables:
tbl_Countries
: 250 values, with columns like CountryName
(Afghanistan), TwoCharCountryCode
(AF), ThreeCharCountryCode
(AFG).Like this:
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
and
population_by_country_2020
: 235 values which has the column Country_or_dependency
(Afghanistan) but is missing the 2 and 3 CHAR
country code.Like this:
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
…yeah, because they have different values they will never match perfectly but I can do those who miss by hands.
What I want to do overall is to move AF
and AFG
from tbl_Countries
to population_by_country_2020
in the row ‘Afghanistan’.
How to archive that?
Is this what you are looking for?
--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 |
Correct answer by Scott Hodgin on January 9, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP