英文:
Update + join from two databases
问题
帮助我创建一个SQL查询。有两个数据库DB1和DB2。在它们每个数据库中都有名为"users"和"cities"的表，其中包含城市的名称。用户和城市名称在两个数据库中都相同。但是城市ID值在两个数据库中是不同的。在第一个DB1数据库中，"users"表包含来自"cities"表的城市ID。
我需要更新第二个DB2数据库中的城市数据。对于来自DB1的每个"users"值，找到"cities"的名称，并更新DB2.users中的值。问题在于两个数据库中的城市ID值是不同的，只有名称相同。
英文:
Help me create an sql query.
There are two databases DB1 and DB2. In each of them there are tables "users" and "cities" with the names of cities. Users and city names are the same in both databases. But the city ID values are different in the two databases. In the first DB1 database, the "users" table contains the city IDs from the "cities" table.
I need to update the city data in the second DB2 database. For each "users" value from DB1, find the name "cities" and update the values in DB2.users. The problem is that the city ID values are different in the two databases. Only the names are the same.
答案1
得分: 0
A 和 B 是两个数据库
使用 cte 作为(
从 A.dbo.tbl_Users 作为 DB1 中选择 DB1.ID, DB1.UserName, (选择 Name from A.dbo.tbl_Cities where Id= DB1.CITY) as 'City'
从 A.dbo.tbl_Users 作为 DB1)
从 B.dbo.tbl_users 作为 DB2
内连接 cte on DB2.ID= cte.ID
内连接 B.dbo.tbl_Cities 作为 DB2Cities on cte.City= DB2Cities.Name
英文:
A and B beign the 2 databases
with cte as(
select DB1.ID, DB1.UserName, (select Name from A.dbo.tbl_Cities where Id= DB1.CITY) as 'City'
from A.dbo.tbl_Users as DB1)
select DB2.ID,DB2.UserName,cte.City from B.dbo.tbl_users as DB2 inner join cte on DB2.ID= cte.ID
inner join B.dbo.tbl_Cities as DB2Cities on cte.City= DB2Cities.Name
答案2
得分: 0
使用带有连接的更新：
update DB2.users set
city = DB2.cities.ID
from DB2.users
join DB1.users on DB1.users.ID = DB2.users.ID
join DB1.cities on DB1.cities.ID = DB1.users.city
join DB2.cities on DB2.cities.name = DB1.cities.name
从DB2.users到DB2.cities的连接通过DB1的表进行，通过使用用户ID前进，然后使用城市名称返回。
英文:
Use an update with join:
update DB2.users set
city = DB2.cities.ID
from DB2.users
join DB1.users on DB1.users.ID = DB2.users.ID
join DB1.cities on DB1.cities.ID = DB1.users.city
join DB2.cities on DB2.cities.name = DB1.cities.name
The join from DB2.users to DB2.cities goes via DB1's tables, going over using user ID and coming back using city name.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论