TransWikia.com

SQL Server :: how to move country code column to another table?

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’.

enter image description here

How to archive that?

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP