Can't set integer field to null

[expired user #4177]'s profile image [expired user #4177] posted 15 years ago in General Permalink
Hello,

I don't know what I'm doing wrong. Here is what I'm doing:

I select a table and go to the 'Data' tab.
I click on a row and enter an INTEGER field.
I delete the contents of the field and then click on a different row.
The following error pops-up:
SQL Error:  Incorrect integer value: '' for column 'zip' at row 1

However, the following sql code works from a command line prompt:
UPDATE table SET zip=null WHERE id=#;

Where table is the name of the table and # is the id for the record that I am updating.

Does anyone know how to enter null values using HeidiSQL? If so, is there a way to enter a null value for lots of records in one shot?
kalvaro's profile image kalvaro posted 15 years ago Permalink
Empty is not the same as NULL. You have to right click on the field and select Set NULL (or just hit Shift+Ctrl+N).
[expired user #4177]'s profile image [expired user #4177] posted 15 years ago Permalink
Fooey. I knew that empty was not the same as NULL, I just couldn't figure out how to set it to null. Thanks!

Also, a simple SQL Query will solve my problem of mass changes:
UPDATE table SET column=NULL WHERE column=0;

Where table is the name of the table and column is the name of column that I want to change.

Please login to leave a reply, or register at first.