sql - Subquery returned more than 1 value regardless of identity for Update statement -

i having issues using sub-query return value need update statement. working crm database here. have used job import fields, need use update statement not fields available in importer.

when imported actions, used actionname field combination of id fields create identity each action in event needed make updates data.

i need update "subject" field using requestorcomments field specific each action (either 2016 conference or 2017 conference), subquery not recognizing combination of string, securityid (fk), contactid (fk) , identity unique value.

i have tried using select top 1 subquery, returns "2017 conference" each row instead.

declare @actions table (contactid nvarchar(256), personid int, completorcomments nvarchar(256), requestorcomments nvarchar(256), securitylistingid int, substatuscode int, completeddate datetime, number int not null identity (1,1)) insert @actions values ('34733','211','corporation1','2017 conference','2648','10014','2017-01-23 00:00:00') insert @actions values ('34733','211','corporation2','2016 conference','9103','10014','2016-01-23 00:00:00')  update dbo.action set subject = (select requestorcomments            @actions            join dbo.action act (nolock)            on act.name = 'trst-1289' + cast(securitylistingid nvarchar) + contactid + cast(number nvarchar)             act.createddate between '2017-07-27 15:00:09.540' , '2017-07-27 15:05:09.540') ,updatedby = 9999 ,updateddate = getdate () --select * @actions join dbo.action act (nolock) on act.name = 'trst-1289' + cast(securitylistingid nvarchar) + contactid +  cast(number nvarchar)  act.createddate between '2017-07-27 15:00:09.540' , '2017-07-27  15:05:09.540' 

you don't want use subquery this. want use joins in update statement. this.

update act set subject = a.requestorcomments     , updatedby = 9999     , updateddate = getdate () @actions join dbo.action act on act.name = 'trst-1289' + cast(securitylistingid nvarchar) + contactid + cast(number nvarchar)  act.createddate between '2017-07-27 15:00:09.540' , '2017-07-27 15:05:09.540' 

you may notice removed nolock hint. bad habit throw hint everywhere , in update can mess things you. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/

you casting varchar did not specify size, critical default size varchar can vary depending on usage. avoid problem entirely specifying size.