I have problem with updating query in Access. I have 3 tables:
我在Access中更新查询时遇到问题。我有3张桌子：
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.
我想用Excel数据更新我的数据库。我决定创建CityImport表以使过程更清晰。我把城市信息放在这个表中，我想用它填充City表。当我运行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，我得到JOIN表达式不支持错误。
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.
我认为这是我的语法问题，但如果我从JOIN中删除一个条件，并将其保留为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，它工作正常。问题是它忽略了不同国家的同名城市。
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.
我玩了几次这个查询的不同拍摄，谷歌搜索了几百次，但仍然没有成功。
1 个解决方案
#1
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.
我能看到的第一个问题是你正在使用UPDATE将数据插入表中。您应该使用INSERT INTO。
Starting with this table:
从这个表开始：
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:
这将给你这个表：
Now you need to populate the City table with city names and the ID's from the Country table:
现在，您需要使用城市名称和Country表中的ID填充City表：
INSERT INTO City (CityName, CountryID)
SELECT City, Country.ID
FROM CityImport INNER JOIN Country ON CityImport.Country = Country.Country
This returns this table:
这将返回此表：
Edit:
编辑：
Table Structure:
CityImport
City - Text
Country - Text
表结构：CityImport City - 文本国家 - 文本
Country
ID - AutoNumber
Country - Text
Primary Key: ID
国家/地区ID - 自动编号国家/地区 - 文本主键：ID
City
ID - AutoNumber
CityName - Text
CountryID - Number
Primary Key: ID
Foreign Key CountryID References Country
城市ID - 自动编号城市名称 - 文本CountryID - 编号主键：ID外键CountryID参考国家/地区
#1
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.
我能看到的第一个问题是你正在使用UPDATE将数据插入表中。您应该使用INSERT INTO。
Starting with this table:
从这个表开始：
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:
这将给你这个表：
Now you need to populate the City table with city names and the ID's from the Country table:
现在，您需要使用城市名称和Country表中的ID填充City表：
INSERT INTO City (CityName, CountryID)
SELECT City, Country.ID
FROM CityImport INNER JOIN Country ON CityImport.Country = Country.Country
This returns this table:
这将返回此表：
Edit:
编辑：
Table Structure:
CityImport
City - Text
Country - Text
表结构：CityImport City - 文本国家 - 文本
Country
ID - AutoNumber
Country - Text
Primary Key: ID
国家/地区ID - 自动编号国家/地区 - 文本主键：ID
City
ID - AutoNumber
CityName - Text
CountryID - Number
Primary Key: ID
Foreign Key CountryID References Country
城市ID - 自动编号城市名称 - 文本CountryID - 编号主键：ID外键CountryID参考国家/地区