时间戳

首页 > 解决方案 > 多条记录的 SQL 更新

sql - 多条记录的 SQL 更新

问题描述

StartDate       Departmentid    DateID
Jan 2010        3               500
Febuary 2011    8               501
March 2012      2               503
April 2013      1               504

更新上面的 departmentID 应该具有 DateID 的值

以上 2 列来自不同的表：

StartDate is from [DatesAvaliable]
DepartmentID is from [Persons]
DateID is from [DatesAvaliable]

用于构建它的查询如下：

select i.startdate,DepartmentID,i.id as DateID 
from persons p 
inner join substitutetable ppk on ppk.id = p.DepartmentID 
inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
where departmentID is not null

这个 Table [substitutetable]种在表之间建立了联系，否则我得到的记录是不可能的

如何编写更新查询来更新上述记录

我可以在哪里用 DateID 替换部门 ID 值？

标签： sqlsql-server

解决方案

If I'm following this correctly, which is confusing to begin with...

But if your select statement gets you what you need, then just turn your select statement into an update statement.

select i.startdate,DepartmentID,i.id as DateID 
from persons p 
inner join substitutetable ppk on ppk.id = p.DepartmentID 
inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
where departmentID is not null

** UPDATE **

Update P
Set DepartmentID = i.id
from persons p 
    inner join substitutetable ppk on ppk.id = p.DepartmentID 
    inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
    where departmentID is not null

推荐阅读