SQL Server：从联接的表中更新多个记录

debugcn 发表于 Dev

戴维·波拉德

我在测试环境中使用SQL Server 2008 R2 Express，并在生产环境中使用完整版本。我写了一条选择语句，该语句查找我要更新的所有记录。其中约有1200个。它联接多个表，并且选择基于多个字段。

我想做的就是将其变成一条update语句，其中所有匹配记录的文件更新都相同，即dFinalized设置为'2015-01-14 00：00：00.000'

运行更新查询时收到以下错误

子查询返回了1个以上的值。当子查询遵循=，！=，<，<=，>，> =或将子查询用作表达式时，不允许这样做。

我希望我将不得不以某种方式嵌套此嵌套，但是我找不到足够简单的示例可以遵循。

预先感谢大卫

select语句非常简单

SELECT lMeetingRegisterID
      ,sPlanNumber
      ,sName
      ,sDescription
      ,dMeeting
      ,sMeetingTime
      ,bManaged

  FROM [Strata].[dbo].[MeetingRegister]
  inner Join MeetingType on MeetingRegister.lMeetingTypeID = meetingtype.lMeetingTypeID
  inner Join OwnersCorporation on MeetingRegister.lOwnersCorporationID = OwnersCorporation.lOwnersCorporationID
  inner Join tbluser on OwnersCorporation.lUserID = tblUser.lUserID

  WHERE dFinalised = '1900-01-01 00:00:00.000'
  AND dMeeting < '2014-07-01 00:00:00.000' 
  AND bManaged != 'N'

这是我对更新查询的尝试。

Update dbo.MeetingRegister
set dFinalised = '2015-01-14 00:00:00.000'
from dbo.MeetingRegister
  inner Join MeetingType on MeetingRegister.lMeetingTypeID = meetingtype.lMeetingTypeID
  inner Join OwnersCorporation on MeetingRegister.lOwnersCorporationID = OwnersCorporation.lOwnersCorporationID
  inner Join tbluser on OwnersCorporation.lUserID = tblUser.lUserID
Where dFinalised = '1900-01-01 00:00:00.000'
  AND dMeeting < '2014-07-01 00:00:00.000' 
  AND bManaged = 'N'

sqluser

尝试表表达式

WITH C AS (
SELECT lMeetingRegisterID
      ,sPlanNumber
      ,sName
      ,sDescription
      ,dMeeting
      ,sMeetingTime
      ,bManaged
      ,dFinalised

  FROM [Strata].[dbo].[MeetingRegister]
  inner Join MeetingType on MeetingRegister.lMeetingTypeID = meetingtype.lMeetingTypeID
  inner Join OwnersCorporation on MeetingRegister.lOwnersCorporationID =    OwnersCorporation.lOwnersCorporationID
  inner Join tbluser on OwnersCorporation.lUserID = tblUser.lUserID

  WHERE dFinalised = '1900-01-01 00:00:00.000'
  AND dMeeting < '2014-07-01 00:00:00.000' 
  AND bManaged != 'N'
)
Update C
set dFinalised = '2015-01-14 00:00:00.000'

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。