SQL Server : update multiple records from joined tables

Question

I'm using SQL Server 2008 R2 Express in my test environment and the full version in production. I have written a select statement that finds all the records I want to update. About 1200 of them. It Joins multiple tables and the selection is based on multiple fields.

What I want is to do is turn this into an update statement where all matching records have the same filed updated i.e. dFinalised is set to '2015-01-14 00:00:00.000'

I receive the following error when I run my update query

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I expect I'm going to have to nest this somehow but I can't find a simple enough example to follow.

Thanks in advance David

The select statement is fairly simple

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'

Here is my attempt at the update query.

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'

It means that your join or subquery returns more than one value and you are trying to assign it to a single holder. You need to trim your subquery to return a single value. — T.S., Jan 14 '15 at 04:56
Do you by any chance have a trigger defined on `MeetingRegister` table? — peterm, Jan 14 '15 at 04:59
@DavidPollard Well this error is coming from your trigger, because most likely it written under false assumption that it processes one row at a time when in reality SQL Server triggers are set based. — peterm, Jan 14 '15 at 05:10

score 1 · Accepted Answer · answered Jan 14 '15 at 04:57

1

Try table expression

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'

answered Jan 14 '15 at 04:57

sqluser

5,502
7
36
50

Looks like a great suggestion. – David P Jan 14 '15 at 05:09
I tried the above query "With C" and it failed. with the same error message. I disabled my triggers before the query and enabled them again straight after and it worked ! I'm not sure if my original code would have worked with the triggers disabled. I'll go and try it anyway. – David P Jan 14 '15 at 05:22
Probably it works. I think the trigger was your problem. But generally I prefer to use CTEs in these cases. – sqluser Jan 14 '15 at 05:23
My original code does work with the triggers disabled. Thanks guys for an extremely quick response and also correct first go. – David P Jan 14 '15 at 05:46

SQL Server : update multiple records from joined tables

1 Answers1