Mysql update statement for decimal fields to NULL or emptiness

Question

Can't figure out, how to update decimal field to null or emptiness.

Have tried:

UPDATE ads SET price=NULL WHERE price=0

And

UPDATE ads SET price="" WHERE price=0

Isn't working.

Thanks ;)

(Update) Just a moment.

Answer 1

UPDATE ads SET price=NULL WHERE price=0

That is the correct way to set a field to NULL. However it will not work if the column does not allow NULL.

If you have the CREATE statement for that table available, check if it says NOT NULL for the price column.

If you don't have the CREATE statements you can run 'DESC ads' on oracle or mysql and see if NULL is allowed.

Answer 2

UPDATE ads SET price=NULL WHERE price=0

Above statement will only work if you have allowed null values for column price.

AND

UPDATE ads SET price="" WHERE price=0

Above statement is not working because setting empty for decimal column is equivalent to set it to 0 value.

As the column already contains 0 value hence it might be giving 0 rows updated message.

Answer 3

I totally agree with the answer of dsclementsen, that's why I upvoted him, but even if your price field can be NULL in your table definition, you might have constraints and triggers which prevent this update.