SQL Server 2005 中如何从另一个表中的相应数据更新一个表中的数据 [英] How to update data in one table from corresponding data in another table in SQL Server 2005

问题描述

我在同一数据库服务器上的不同数据库中有两个表.

I have two tables in different databases on the same database server.

两个数据库的结构相同，但数据不同.Database1 (Test1) 是最新的，database2 (Test2) 是数据库的旧副本.

Both the databases have the same structure, but different data. Database1 (Test1) is the latest, and database2 (Test2) is an old copy of the database.

• Test1 有一个名为 Employee 的表，有 3000 条记录
• Test2 有一个名为 Employee 的表，有 1000 条记录

• Test1 has a table called Employee with 3000 records
• Test2 has a table called Employee with 1000 records

我需要从 Test2 中的同一个表中为名为 DeptID 的特定列更新 Test1 中的表，因为 Test2 DB(旧的)中的 Employee 表中的值已更新.所以我需要从旧数据库中的表更新新数据库中的表，该表有大约 1000 行.

I need to update the table in Test1 from the same table in Test2 for a particular column called DeptID, because the values in the Employee table in the Test2 DB (the old one) have been updated. So I need to update the table in the new DB from the table in the old DB which has around 1000 rows.

换句话说，我需要使用我在数据库中的任何值更新 Test1 数据库中 Employee 表中的 DeptID 列Test2 数据库中 Employee 表中的 DeptID 列.

In other words, I need to update the DeptID column in the Employee table in the Test1 DB with whatever values I have in the DeptID column in the Employee table in the Test2 DB.

我知道我可以恢复数据库本身，但这不是解决方案.我需要从 Test2 数据库更新 Test1 数据库中的值.

I know I can restore the DB itself, but that's not a solution. I need to update the values in the Test1 database from the Test2 database.

推荐答案

如果两个数据库在同一台服务器上，你应该能够创建这样的 SQL 语句:

If the two databases are on the same server, you should be able to create a SQL statement something like this:

UPDATE Test1.dbo.Employee
SET DeptID = emp2.DeptID
FROM Test2.dbo.Employee as 'emp2'
WHERE
   Test1.dbo.Employee.EmployeeID = emp2.EmployeeID

从您的帖子中，我不太清楚您是否要使用 Test2.dbo.Employee<中的值更新 Test1.dbo.Employee/code>(这就是我的查询所做的)，或者反过来(因为您提到 Test1 上的 db 是新表......)

From your post, I'm not quite clear whether you want to update Test1.dbo.Employee with the values from Test2.dbo.Employee (that's what my query does), or the other way around (since you mention the db on Test1 was the new table......)

这篇关于SQL Server 2005 中如何从另一个表中的相应数据更新一个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！