CODEWITHSUNDEEP
mysqlalter-table
user2172424
user2172424

Reputation: 51

Mysql Change Int default value zero to null

Hi i'm working laravel 5 with mysql. I did store zipcode into my database it working well but when the field is empty it stored zero(0) but i want to store empty value in database.

I'm also changed mysql using alter table like below ALTER TABLE insurances CHANGE COLUMN zipcode4 zipcode4 int(4) unsigned DEFAULT NULL; but it wont work, have any idea ?

Thanks in advance

Upvotes: 4

Views: 8175

Answers (3)

Muhammad Saqib Patel
Muhammad Saqib Patel

Reputation: 1

100 % accuracy this code

UPDATE mytable SET zipcode4 = NULL WHERE zipcode4 = 0
UPDATE mytable SET Myfield = NULL WHERE Myfield = 0

Upvotes: -2

Luca C.
Luca C.

Reputation: 12564

the prevous default were 0, so after changing the default they still keep it, to update it after having changed the default, do something like:

UPDATE mytable set zipcode4=NULL WHERE zipcode4=0

the new values will correctly put NULL if not value is provided

Upvotes: 0

Hytool
Hytool

Reputation: 1368

Well, zipcode is something which has fixed characters, and wont need any arithmetic operations so,

  1. You can change the datatype to CHAR or VARCHAR

  2. Forcefully insert NULL value while inserting query !

Try This,

3.

 ALTER TABLE insurances CHANGE zipcode4 zipcode4 int(4) DEFAULT NULL;

Upvotes: 3

Related Questions