How to update a SQL Server table that has a column defined as TEXT with UPDATETEXT
I have tried
UPDATETEXT db.tablename
SET ColumnName = ColumnName (‘data’)
WHERE UserID = ‘myID’
I get an error:
Incorrect syntax near the keyword 'SET'.
It works fine if I use NVARCHAR
UPDATE
SET...
WHERE..
Any help will be greatly appreciated.
Your syntax is all off. A correct usage of UPDATETEXT would look like this
DECLARE @ptrval BINARY(16);
SELECT @ptrval = TEXTPTR(t.ColumnName)
FROM tablename t
WHERE UserID = 'myID';
IF @ptrval IS NOT NULL
UPDATETEXT tablename.ColumnName @ptrval 0 NULL 'data';
However, you don't actually need it at all, as you are replacing the whole value. It's only really useful if you want to do a partial update on a big value. You might as well do a normal update.
UPDATE tablename
SET ColumnName = 'data'
WHERE UserID = 'myID';
Ideally, you wouldn't use text or ntext at all, as it's deprecated.
ALTER TABLE tablename ALTER COLUMN ColumnName varchar(max);
Then you can still do that UPDATE, or if you want a partial update you can use .WRITE
UPDATE tablename
SET ColumnName.WRITE('data', 0, NULL)
WHERE UserID = 'myID';