August 22, 2005 at 8:55 am
hello,
i would like to update a column called description in a table called
activity.
description is text
i would like to search for those rows where activity.reg_flag = 1
...And update the text at the end of the feild to add:
<br><img src='..\artwork\checkon.jpg'>
there is text before but should not be text after the tag.
i looked at the books online and could not get the syntax correct.
am i using the right command? updatetext?
thanks in advance.
August 22, 2005 at 10:23 am
--Warning: do not work with yr original table, b/c the Q will change the -----data in it; instead work with a copy of the table
--try:
update dbo.Activity_COPY
set description = description + '<br><img src=''..\artwork\checkon.jpg''>'
where reg_flag = 1
--'' are 2 single quotes , not double quotes
it's probably not going to give u what u want immediately, but hopefully u can get an idea how to tweak the Q to make it work.
August 23, 2005 at 12:47 pm
If activity.description is of type text, then you can't use syntax like
set description = description + '<br><img src=''..\artwork\checkon.jpg''>'
Try the following example regarding the use of the UPDATETEXT statement. Since you didn't post the full table definition, I've assumed a primary key called [id].
--DROP TABLE activity
GO
SET NOCOUNT ON
CREATE TABLE activity
(
[id] int NOT NULL IDENTITY PRIMARY KEY CLUSTERED
, reg_flag int
, [description] TEXT NULL
)
INSERT INTO activity ( reg_flag, [description] ) VALUES (0, 'a')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 0, 'b')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 1, 'c d')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 1, 'e')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 0, '<')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 0, '<f')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 1, '<g ' + space(7000) )
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 0, '<h j k l m ')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 1, 'This is a test')
INSERT INTO activity ( reg_flag, [description] ) VALUES ( 0, NULL )
SET NOCOUNT OFF
DECLARE @id int, @tptr varbinary(16), @tlen int
DECLARE @textToAppend varchar(8000)
SET @textToAppend = '<br><img src=''..\artwork\checkon.jpg''>'
SELECT @id = Min(id)
FROM activity
WHERE activity.reg_flag = 1
WHILE @id IS NOT NULL
BEGIN
SELECT @tPtr = TEXTPTR([description])
FROM activity
WHERE id = @id
UPDATETEXT activity.[description] @tPtr NULL 0 @textToAppend
-- get next row to process
SELECT @id = Min(id)
FROM activity
WHERE activity.reg_flag = 1
AND id > @id
END --WHILE
SELECT [id], reg_flag, [description] FROM activity
August 23, 2005 at 1:11 pm
Yeah - That was the problem I was facing... but the idea of making a copy of the table got me thinking. and I think I solved the problem.
I can't alter the db structure or objects or I'll void the apps warranty, so I created a temp table (temp_activity).
Because I need this job to run every day, I created a new stored procedure that populated the temp table with the applicable primary key and description field - which I've made varchar.
I update the field with the tag, then updated the applicable activities on my "production" table with the new description text.
Then the temp table is cleared and the job runs again the next day.
thanks for everyone's help though!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply