Update Text

Question

Update Text

Amy Freeman

Newbie

Points: 2
More actions
December 28, 2004 at 2:34 pm

#102328

I have searched the forums and read the various posts about updating text but I can't get them to work.
I need to take data from a userdefined field in table A and append it to an existing notes(text) field in table B.
DECLARE @ptrval varbinary(16), @txtFieldLen int, @txtToAdd int, @notelen int
SELECT @ptrval = TEXTPTR(notes)
FROM Prospect pr where prospectidy = 3
SELECT @NoteLen = datalength(notes) from Prospect pr
      WHERE pr.ProspectIDY = 3
SET @txtToAdd = (Select * from userdefinedfieldvalue udfv
  where exists(
               select udfv.prospectidy
        from UserDefinedFieldvalue udfv
               where udfv.userdefinedtypeidy = 45
        and udfv.fieldvalue is not null
        and udfv.prospectidy = 3)and userdefinedfieldvalue.userdefinedtypeidy = 45)
UPDATETEXT Prospect.Notes @ptrval @notelen null @txtToAdd
I am having issues with the section where I set the txtToAdd variable. I get 'The column prefix 'userdefinedfieldvalue' does not match with a table name or alias name used in the query.'
I need to have txtToAdd equal the fieldvalue of the row that has both the prospectidy of 3 and the userdefinedtypeidy of 45.
I then need to append it to the existing prospect.notes field with 'Transfer Date: ' preceeding it.
I have been banging away at this all day with no luck. I am sure it is something simple, but I am a newbie and do not have a full handle on SQL yet.
Thanks for any help.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

vadba SSChampion Points: 11133 More actions · Answer 1

I think you've made your code unnecessarily complex. It seems as though you should be able to set @txtToAdd with this query:

SELECT @txtToAdd = 'Transfer Date: ' + udfv.fieldvalue

FROM UserDefinedFieldvalue udfv

WHERE udfv.userdefinedtypeidy = 45

AND udfv.fieldvalue IS NOT NULL

AND udfv.prospectidy = 3

Also,

To append text to the end of a text column, you should'nt need the length of the Notes column:

UPDATETEXT Prospect.Notes @ptrval null null @txtToAdd

Good luck,

Mike

Amy Freeman Newbie Points: 2 More actions · Answer 2

Thanks!! It seems I often try to over complicate things.

Thanks for your help, it worked great.