Nicolas D Nicolas D - 1 year ago 244
SQL Question

data truncation error on null field

I am cleaning my db of obsolete values, and I want to set a column is_promotion to null :

update my_table
set is_promotion = null
where is_promotion = 0

And I have this error

Data Truncation error occurred on a write of column 0Data was 0 bytes long and 0 bytes were transferred.

Note that my_table have this structure for my column is_promotion, so it should be nullable :

`is_promotion` varchar(10) DEFAULT NULL,

Please note that I saw some posts about data truncation, but all of them were because column wasnt formatted for the provided value, in my case my column is nullable

Answer Source

The problem is that is_promotion is a varchar, so you should use:

WHERE is_promotion = '0' 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download