CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Fung
Fung

Reputation: 7790

How can I conditionally update a text/ntext field in SQL?

In a stored procedure I'm trying to conditionally update a field (like the 2nd line in the SQL statement below)

UPDATE [some_stuff] SET
  last_update = CASE WHEN val = @NewVal THEN last_update ELSE GETDATE() END,
  val = @NewVal

...but for a text/ntext field. What's the most efficient way to go about doing that? Does it have to be a separate UPDATETEXT statement? And do I have to do an extra SELECT first?

Upvotes: 0

Views: 1007

Answers (2)

Dave Barker
Dave Barker

Reputation: 6447

The above example will work in SQL Server 2005 where val is a Text field and you're updating the whole value. If you're only replacing part of a field then use UPDATETEXT in a separate statement.

A better solution, if you can update the schema is to use VARCHAR(MAX) or NVARCHAR(MAX) columns. The UPDATETEXT command has been marked as deprecated in a future release of SQL Server. If you're using these data types then you can use the column_name.WRITE in the UPDATE statement to replace part of a value.

Upvotes: 3

JohnFx
JohnFx

Reputation: 34917

Maybe the example isn't realistic, but couldn't you simplify this down to:

UPDATE [some_stuff] 
SET last_update =  GETDATE()
WHERE val != @NewVal

The first half of the case just sets the field to itself, which seems kinda pointless.

Upvotes: 0

Related Questions