0

I have problem with updating query in Access. I have 3 tables:

City
ID | CityName | CountryID|

Country
ID | CountryName

CityImport
City | Country

I'm not sure if it's the right design, but it's of a lesser importance now.

I want to update my db with Excel data. I decided to create CityImport table to make the process clearer. I put city information in this table and I want fill City table with it. When I run a query like UPDATE (CityImport INNER JOIN Country ON CityImport.Country = Country.CountryName) LEFT JOIN City ON City.CityName = CityImport.City AND City.CountryID = Country.ID SET City.CityName = CityImport.City, City.CountryID = Country.ID WHERE CityImport.City IS NOT NULL, I get JOIN expression not supported error.

I thought it was a problem with my syntax, but if I remove one condition from JOIN, and leave it as UPDATE (CityImport INNER JOIN Country ON CityImport.Country = Country.CountryName) LEFT JOIN City ON City.CityName = CityImport.City SET City.CityName = CityImport.City, City.CountryID = Country.ID WHERE CityImport.City IS NOT NULL, it works fine. The problem is that it ignores cities with the same name in different countries.

Is it possible to make such join work properly somehow? Or is it incorrect by definition? It requires to join one one table with another join results on two columns from different tables. I could probably work around it somehow in this case, but I want to use the same method for more, more complicated tables.

I played around with different takes on this query for few hours, googled hundred times, but still no success.

brainac
  • 400
  • 3
  • 12
  • http://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query?rq=1 – Dave Mar 03 '17 at 10:11
  • @Dave, it does not address my exact problem. I know how to join tables, what I cannot get to work is joining them on fields from two different tables that are in nested join. – brainac Mar 03 '17 at 10:31

1 Answers1

1

The first problem I can see is that you're using UPDATE to insert data into a table. You should be using INSERT INTO.

Starting with this table:
enter image description here

You'll need to insert the unique Country names into the country table first:

INSERT INTO Country (Country)
SELECT DISTINCT Country
FROM CityImport

This will give you this table:
enter image description here

Now you need to populate the City table with city names and the ID's from the Country table: 

INSERT INTO City (CityName, CountryID)
SELECT City, Country.ID
FROM  CityImport INNER JOIN Country ON CityImport.Country = Country.Country

This returns this table:
enter image description here

Edit:

Table Structure:
CityImport
City - Text
Country - Text

Country
ID - AutoNumber
Country - Text
Primary Key: ID

City
ID - AutoNumber
CityName - Text
CountryID - Number
Primary Key: ID
Foreign Key CountryID References Country

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Thanks for suggestion. As for `CountryName`, it's just a typo, but good catch. I know I can use insert in this case, but I want to use this method for other tables, and to be able not only add new records, but also update existing ones. – brainac Mar 03 '17 at 11:18
  • Thought I was having a senior moment then - looked again and could only see `CityName` so removed my edit pointing it out, but now can see it again. I really need to retire and spend my days tending my garden. :) – Darren Bartrup-Cook Mar 03 '17 at 11:20
  • I appreciate the effort :) I probably can work it around by creating additional temporary table from the first join, but I hoped to do it the **right** way. In general it's hard to find good practices when it comes to databases. So many different approaches around. – brainac Mar 03 '17 at 11:33