Update and Varbinary(Max)

Question

I want to set the value of sequence from row 1 to row 2.

If I run:

Update [dbo].[TS_TaskSequence] 
set [Sequence] = 'the value of sequence' 
Where [TS_ID] = '16777234'

I get:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

How do I convert it correctly?

Answer 1

You need to CAST() (CONVERT()) the hard-coded value in your query as a VARBINARY(MAX) because by default a value in single quotes in a query is natively treated as a VARCHAR data type.

You could also use the following CROSS JOIN query (without hard-coding the value) to completely eliminate the need to cast anything since your source and destination fields in the query are already both of type VARBINARY(MAX):

UPDATE TS2
SET TS2.Sequence = TS1.Sequence
FROM dbo.TS_TaskSequence AS TS1
CROSS JOIN dbo.TS_TaskSequence AS TS2
WHERE TS1.TS_ID = '16777230'
    AND TS2.TS_ID = '16777234'