MySQL ALTER/UPDATE table

Question

I have a table that contains NULL values. This table is meant only to store numerical values, except the second column which contains a time-stamp for each record. This table has been in use for some time and so has accumulated a lot of NULL values in varying columns. Here's the table's description:

+-----------------------------------------+-----------+------+-----+-------------------+----------------+
| Field                                   | Type      | Null | Key | Default           | Extra          |
+-----------------------------------------+-----------+------+-----+-------------------+----------------+
| results_id                              | int(11)   | NO   | PRI | NULL              | auto_increment |
| time_stamp                              | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| test_col                                | int(11)   | YES  |     | NULL              |                |
| test_col-total                          | int(11)   | YES  |     | NULL              |                |
| test_col_B                              | int(11)   | YES  |     | NULL              |                |
| test_col_B-total                        | int(11)   | YES  |     | NULL              |                |
+-----------------------------------------+-----------+------+-----+-------------------+----------------+

12 rows in set (0.01 sec)

I now want to UPDATE/ALTER the table so that:

• from now on any NULL value being added to the table is handled and processed as a '0' value instead (really interested to know if this is indeed possible; if it is then I wont need to change a load of INSERT queries in a lot of my Python scripts elsewhere!)
• all stored NULL values are updated/changed to '0'.

I am entirely stuck with this because on the one hand I want my SQL query to update a new rule to the table while on the other change current NULL values and as a novice this is a little more intermediate for my current understanding.

So far I have:

ALTER TABLE `results` MODIFY `<col_name>` INT(11) NOT NULL;

And I will do this for each column that currently allows NULL values. However, I do not know how to change stored NULL values to '0'.

Any input appreciated.

Answer 1

to change NULL values to 0 try

UPDATE results SET `col_name` = 0 WHERE `col_name` IS NULL;

to change columns to have NOT NULL and default to 0 try

ALTER TABLE results MODIFY `col_name` INT(11) NOT NULL DEFAULT 0;

you have to do it in the above order, i just tested this on http://sqlfiddle.com/

Answer 2

First change your values to 0 where they are null:

UPDATE results SET col1 = 0 WHERE col1 IS NULL;
...

Then you can add a DEFAULT of 0, that will be added whenever you supply no values to that table on an insert

ALTER TABLE `results` MODIFY `<col_name>` INT(11) NOT NULL DEFAULT 0;