check null or not

Question

I have this table in my database, and i need to add 1 each time. unless if it's null I want to set it to one. I've thought of make the default value zero, but i need the zero too.

for example

-----------------------
id      number
-----------------------
01       NULL   
02        3
03        1

i don't want to run 2 queries.

QUERY1 : SELECT number and store it in variable

if($number == NULL) $number = 1
else
{
$number = $number++
}

QUERY2 : UPDATE number

is there any 1 SQL Query can do this for me without process the data in php ?

thank's

Answer 1

Yes, you can detect whether a value is NULL or not. Do this:

UPDATE mytable SET number = IF(ISNULL(number), 1, number+1)

This will set any number that's NULL to 1, and increase the other numbers by one. So this:

-----------------------
id      number
-----------------------
01       NULL   
02        3
03        1

will yield:

-----------------------
id      number
-----------------------
01        1 
02        4
03        2

This way, you don't have to go through PHP anymore.

Answer 2

UPDATE table SET number=IF(number IS NULL,1,number+1)

That should do the trick.