I have been teaching myself as I go and having trouble updating a varbinary column. The issue is the column has a different data type. I want to convert varbinary to bit.

My code looks like:

use databasename
update table set column = '1' where column in ( ' ', ' ', ' ')

I know I need a convert in there however I am unsure of the order. I want the value for an isdeleted column to return all '1'.

closed as unclear what you're asking by Scott Hodgin, mustaccio, Max Vernon, jadarnel27, McNets Feb 6 at 18:21

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


This has been answered for you already, twice, in the past 14 hours on Stack Overflow, including a pretty lengthy and explanatory yet unappreciated (and since deleted) answer by me. Let's keep it simple:

To update the column, you need to use an explicit conversion:

UPDATE table SET column = CONVERT(varbinary(8), '1') WHERE ...;

The string '1' is stored in varbinary as 0x31. If you select from the table, that's what you'll see. To see it as you entered it, you need to convert it back:

SELECT column, CONVERT(varchar(32), column) FROM table WHERE ...;

If you don't like having to convert a string to and from varbinary, stop using varbinary to store values like 0 and 1 - you should be using a bit column in the first place. As has been suggested multiple times. If you can't change it, you'll need to learn how to work with it. There are no short cuts.

Not the answer you're looking for? Browse other questions tagged or ask your own question.