SQL两个库批量修改问题

[ccmaojiushiwo]

两个库批量修改问题

A库
员工部门
Department
DepartmentID DepartmentName
员工表 
Employee
EmployeeID EmployeeStrID EmployeeName DepartmentID

B库
员工部门
Department
DepartmentID DepartmentName
员工表 
Employee
EmployeeID EmployeeStrID EmployeeName DepartmentID


 UPDATE B.dbo.Employee set EmployeeStrID=esi ,EmployeeName=en,DepartmentID=ISNULL((SELECT Top 1 DepartmentID FROM B.dbo.Department WHERE b.DepartmentName=dn Order by DepartmentID desc),1)
 FROM B.dbo.Employee a LEFT  JOIN B.dbo.Department b ON a.DepartmentID=b.DepartmentID 
 INNER JOIN 
 (SELECT EmployeeName en,EmployeeStrID esi,DepartmentName dn FROM A.dbo.Employee c LEFT  JOIN A.dbo.Department d ON c.DepartmentID = d.DepartmentID) e ON a.EmployeeStrID=e.esi AND(a.EmployeeName!=e.en OR b.DepartmentName!=e.dn)

有些是员工没有部门的，所以我用了left，都是烂数据，我想把B库跟A库根据员工号来同步，把名字和部门弄成相同的，B库的部门已经建成跟A库一样了，通过A的部门名来找到B的部门ID给B的员工，同步好像有问题，一些明明有部门名的ID不是为1的都变成了1，大侠们帮我看看
分数只有这么多了，还有另外个帖子没结，要分可以回答那个

[快溜]

先同步部门表，再同步员工表，写的太乱了。 UPDATE a seta. EmployeeStrID=b.EmployeeStrID,a.EmployeeName=b.EmployeeName。。。。 from B.dbo.Employee a inner join A.dbo.Employee b on a.EmployeeStrID=b.EmployeeStrID

[ccmaojiushiwo]

引用 1 楼 ssp2009 的回复:

先同步部门表，再同步员工表，写的太乱了。 UPDATE a seta. EmployeeStrID=b.EmployeeStrID,a.EmployeeName=b.EmployeeName。。。。 from B.dbo.Employee a inner join A.dbo.Employee b on a.EmployeeStrID=b.EmployeeStrID

好吧，弄了一下午，搞不灵清了，吃了顿饭回来， 一个仔细看了终于发现了问题

UPDATE B.dbo.Employee set EmployeeStrID=esi ,EmployeeName=en,DepartmentID=ISNULL((SELECT Top 1 DepartmentID FROM B.dbo.Department WHERE b.DepartmentName=dn Order by DepartmentID desc),1)
 FROM B.dbo.Employee a LEFT  JOIN B.dbo.Department b ON a.DepartmentID=b.DepartmentID 
 INNER JOIN 
 (SELECT EmployeeName en,EmployeeStrID esi,DepartmentName dn FROM A.dbo.Employee c LEFT  JOIN A.dbo.Department d ON c.DepartmentID = d.DepartmentID) e ON a.EmployeeStrID=e.esi AND(a.EmployeeName!=e.en OR b.DepartmentName!=e.dn)

终于可以好好睡觉了

[ccmaojiushiwo]

UPDATE B.dbo.Employee set EmployeeStrID=esi ,EmployeeName=en,DepartmentID=ISNULL((SELECT Top 1 DepartmentID FROM B.dbo.Department WHERE <span style="color: #FF0000;">b.</span>DepartmentName=dn Order by DepartmentID desc),1) FROM B.dbo.Employee a LEFT JOIN B.dbo.Department b ON a.DepartmentID=b.DepartmentID INNER JOIN (SELECT EmployeeName en,EmployeeStrID esi,DepartmentName dn FROM A.dbo.Employee c LEFT JOIN A.dbo.Department d ON c.DepartmentID = d.DepartmentID) e ON a.EmployeeStrID=e.esi AND(a.EmployeeName!=e.en OR b.DepartmentName!=e.dn)

[ccmaojiushiwo]

UPDATE B.dbo.Employee set EmployeeStrID=esi ,EmployeeName=en,DepartmentID=ISNULL((SELECT Top 1 DepartmentID FROM B.dbo.Department WHERE b.DepartmentName=dn Order by DepartmentID desc),1) FROM B.dbo.Employee a LEFT JOIN B.dbo.Department b ON a.DepartmentID=b.DepartmentID INNER JOIN (SELECT EmployeeName en,EmployeeStrID esi,DepartmentName dn FROM A.dbo.Employee c LEFT JOIN A.dbo.Department d ON c.DepartmentID = d.DepartmentID) e ON a.EmployeeStrID=e.esi AND(a.EmployeeName!=e.en OR b.DepartmentName!=e.dn)