CODEWITHSUNDEEP
sqlimagesql-server-2008
Andrey Baulin
Andrey Baulin

Reputation: 679

Update an image column using a variable

I need to copy a value of an image column from one row to another. I tried to use variables for that, but I can't even declare that type of variable. SQL Server returned this error message:

Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

Upvotes: 0

Views: 5601

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

Well the image data type has been deprecated, so you should be changing your tables and all of your code to use varbinary(max) (you will have to do this at some point, the sooner the better). An easy workaround in the meantime would be to use varbinary(max) in your variable:

DECLARE @x TABLE(i IMAGE);

INSERT @x SELECT 0x0045;

DECLARE @y VARBINARY(MAX);

SELECT @y = i FROM @x;

PRINT @y;

-- UPDATE sometable SET somecolumn = @y WHERE ...;

However if you are performing an update, why do you need to use an intermediate variable in the first place? You can join two rows in the same table quite easily...

UPDATE t
  SET t.image_column = s.image_column
  FROM dbo.table AS t
  INNER JOIN dbo.table AS s
  ON t.key = something
  AND s.key = something_else;

Sample:

DECLARE @x TABLE(x INT PRIMARY KEY, i IMAGE);

INSERT @x SELECT 1,0x0045;
INSERT @x SELECT 2,0x0055;

UPDATE x SET x.i = y.i
  FROM @x AS x
  INNER JOIN @x AS y
  ON x.x = 1 AND y.x = 2;

SELECT x,i FROM @x;

Results:

x   i
--- ------
1   0x0055
2   0x0055

Upvotes: 3

Related Questions