I am trying to UPDATE a table from a CSV values that i am fetching from another table. This CSV value is output of a subquery that i created on the basis of variable in #Form table. Now i want to update the #Form table again and i dont want to use cursor.
I am unable to update the #Form table correctly.
Below is the code i am using.
SET AuditorComment = (
Select @Comment = @Comment+', '+a.Value
FROM #Form fd
LEFT JOIN AuditData a on (fd.FormID=a.objectID and a.ObjectTypeID=12 )
WHERE FORM_OID=@frmOID AND SubjectID=@subjectId AND f.FormID = fd.FormID
From #Form f
This isn't going to work like that. You need to use the
for xml path or a similar method:
update f set AuditorComment = stuff((select ', ' + ad.value from formdata fd join AuditData ad on fd.FormID = ad.objectID and ad.ObjectTypeID = 12 where FORM_OID = @frmOID and SubjectID = @subjectId and f.FormID = fd.FormID for xml path ('') ), 1, 2, '') From #Form f;