Nimble Fungus Nimble Fungus - 3 months ago 8
SQL Question

Issue in Updating the same table

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.

Update f
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


Kindly help me where i am doing it wrong.

Answer

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;