SQL Select rows from 1st table and Update into 2nd Table

Question

I have two tables, tblCity and tblCountry and there's no relationship between them at the moment. I need to add CountryIds from tblCountry into tblCity.

This is tblCity:

This is tblCountry:

I need to UPDATE tblCity.CountryId (which is NULL at the moment) with corresponding tblCountry.CountryId

I have ISO2 and ISO3 country codes in both tables, so please help me with select and update queries for SQL Server.

Cᴏʀʏ · Accepted Answer · 2018-01-02T04:27:32.227

2

This statement will match on both ISO2 and ISO3 columns to do the update:

UPDATE
    ci
SET
    ci.CountryId = co.CountryId
FROM
    tblCity ci
JOIN
    tblCountry co
    ON
    ci.ISO2 = co.CountryISO2
    AND
    ci.ISO3 = co.CountryISO3
WHERE
    ci.CountryId IS NULL

edited Jan 02 '18 at 04:27

answered Jan 01 '18 at 21:15

Cᴏʀʏ

105,112
20
162
194

1

And let's avoid updates that are not necessary by adding: "and ci.CountryId is null;" It may not be needed now but these sorts of things are often repeated over time. – SMor Jan 02 '18 at 01:04
@SMor: That's a good suggestion, I'll incorporate it if you don't mind. – Cᴏʀʏ Jan 02 '18 at 04:27

SQL Select rows from 1st table and Update into 2nd Table

1 Answers1

Linked