CODEWITHSUNDEEP
mysqlsqlsql-update
Matthew Spahr
Matthew Spahr

Reputation: 105

Cannot set column = 0 when it is 1, "Data too long for column..."

I am using a column in my MySQL DB to represent complete and incomplete. The column is called status and it's datatype is BIT(1). Every row starts at 0 and ends up being set to 1, this works no problem. I run into a problem when I try to reassign the value to 0. I get an error saying 

 ERROR 1406: 1406: Data too long for column 'status' at row 1
 SQL Statement:
 UPDATE `todolist`.`tasks` SET `status` = '0' WHERE (`id` = '2ea91f19-e8d4-4caf-8583- 
 4bdaff276ca3')

In this example, the id is just the id of the row I am trying to edit. Thanks for your help!

Upvotes: 1

Views: 1801

Answers (3)

iamsam TheAlchemist
iamsam TheAlchemist

Reputation: 49

answer: instead of = try IN

UPDATE 'todolist'.'tasks' SET 'todolist'.'status' = 0 WHERE id in ('2ea91f19-e8d4-4caf-8583-4bdaff276ca3')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

You should be assigning the status BIT column to a bit literal 0, without single quotes:

UPDATE todolist.tasks
SET status = 0
WHERE id = '2ea91f19-e8d4-4caf-8583-4bdaff276ca3';

I am actually suprised that MySQL was not able to do an implicit cast here, but maybe it cannot.

Upvotes: 2

zip
zip

Reputation: 4061

If you set the column as bit, the values accepted are 0 or 1. different from '0' and '1'

 UPDATE `todolist`.`tasks` SET `status` = 0 WHERE (`id` = '2ea91f19-e8d4-4caf-8583- 
 4bdaff276ca3')

Upvotes: 1

Related Questions