mysql update increment int field that is null

mysql null increment
sa511 picture sa511 · Mar 20, 2011 · Viewed 10.3k times · Source

I have a very large table with two INT columns that are null on Default. This is a problem because since they are INT fields, it would help in many cases if they were originally set to 0.

So my questions are, is there a way I can UPDATE and INCREMENT(+1) these fields while they are like this (null on Default)? BTW.. I didn't have luck so far, it seems increment only works when the default=0

..or is my only option to Change the Default to none from null

Answer

Charles picture Charles · Mar 20, 2011 
UPDATE TableName SET column = IFNULL(column, 0) + 1 WHERE ...

More info on IFNULL. It returns the first argument if it is not NULL, the second otherwise.

Related questions

MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of …

Read More
MySQL and PHP - insert NULL rather than empty string

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do …

Read More
Checking for an empty field with MySQL

I've wrote a query to check for users with certain criteria, one being they have an email address. Our site will allow a user to have or not have an email address. $aUsers=$this->readToArray(' SELECT `userID` FROM `…

Read More