MySQL Update NULL Field with Field=Field+1 Strange Behavior

Question

not sure if u face this strange behavior on MySQL table before: when the table field is default as NULL, I do a simple UPDATE statement run no problem. if I were to issue field=field+1 then the value is not update. Then what i did is to insert 0 into the field and run the same field=field+1 again then it works.

UPDATE table1 SET field=field+1 WHERE id=123;

is this expected behavior?

p/s: the field type is double

can you please share your query? – Murad Hasan Apr 04 '16 at 12:44 — Murad Hasan, Apr 04 '16 at 12:44

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

If you have a null field then for update use.

//structure
UPDATE TableName SET column = IFNULL(column, 0) + 1 WHERE ...

//original
UPDATE table1 SET field = IFNULL(field, 0) + 1 WHERE id=123;

More details: function_ifnull

Ref# mysql-update-increment-int-field-that-is-null

edited May 23 '17 at 11:50

Community

1
1

answered Apr 04 '16 at 12:46

Murad Hasan

9,565
2
21
42

my temporary solution is to change the field default value to zero (0), not sure this will works. – d4v1dv00 Apr 04 '16 at 12:47
this will initialize the field to zero first and the increase it. – Murad Hasan Apr 04 '16 at 12:50
1

cool. that will work for me since i have some other fields like that and i don't wish to have so many IFNULL function check. thanks – d4v1dv00 Apr 04 '16 at 12:52

score 1 · Answer 2 · answered Apr 04 '16 at 12:51

1

It's because

NULL+1 = NULL

So you have to "trick" it to 0.

UPDATE table1 SET field=COALESCE(field,0)+1 WHERE id=123;

answered Apr 04 '16 at 12:51

Daniel E.

2,440
1
14
24

MySQL Update NULL Field with Field=Field+1 Strange Behavior

2 Answers2