mysql update increment int field that is null

Question

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

score 49 · Accepted Answer · answered Mar 20 '11 at 07:08

49

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.

answered Mar 20 '11 at 07:08

Charles

50,943
13
104
142

Thank you :> so simple, but saved my day! – Jan Mirus Oct 12 '21 at 09:35
saved my night! :-) – Cris Sep 30 '22 at 02:35

score 3 · Answer 2 · answered Mar 20 '11 at 07:18

Try setting the field as NOT NULL to get away with the problem so that default value of 0 is used instead of null. The other option is to set column as zero whenever it is null.

UPDATE TableName SET FieldName = '0' WHERE FieldName IS NULL

Other alternative would be to issue IFNULL to return 0 in case the column is null and then incrementing the column.

UPDATE TableName SET FieldName = IFNULL(FieldName,0)

score 0 · Answer 3 · answered May 01 '18 at 13:10

The SQL standard would be to use COALESCE(); this has been available in MySQL since version 3.23 (which was released into production in 2001):

UPDATE mytable
   SET mycolumn = COALESCE(mycolumn, 0) + 1
 WHERE my_other_columns = ...

I can't see any reason to choose IFNULL() over COALESCE() here.

Hope this helps.

mysql update increment int field that is null

3 Answers3

Linked

Related