update all NULL fields MySQL

Question

Dear all,

i´d like to update all NULL fields in one table to 0. Of course

UPDATE mytable SET firstcol=0 WHERE firstcol IS NULL 

would do the job. But I wonder if there´s a smarter solution than just c&p this line for every column. Thx in advance for any suggestions

Answer 1

Not without an intermediate technology or cursor. You could use DESCRIBE mytable; to get the column names and loop over them to build your UPDATE queries.

So it is possible. But by the time it took you to write that, you probably just could have copy and pasted ;)

Answer 2

You may want to alter your columns to NOT NULL.

ALTER TABLE your_table MODIFY COLUMN your_field INT NOT NULL;

Test case:

CREATE TABLE nulltable (id INT);

INSERT INTO nulltable VALUES (1);
INSERT INTO nulltable VALUES (2);
INSERT INTO nulltable VALUES (3);
INSERT INTO nulltable VALUES (NULL);
INSERT INTO nulltable VALUES (NULL);
INSERT INTO nulltable VALUES (NULL);
INSERT INTO nulltable VALUES (5);

Result:

mysql> SELECT * FROM nulltable;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
| NULL |
| NULL |
| NULL |
|    5 |
+------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE nulltable MODIFY COLUMN id INT NOT NULL;
Query OK, 7 rows affected, 3 warnings (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 3

mysql> SELECT * FROM nulltable;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  0 |
|  0 |
|  0 |
|  5 |
+----+
7 rows in set (0.00 sec)

Answer 3

Can you just ALTER the columns to NOT NULL DEFAULT 0?

You can do this in a single statement, as per MySQL documentation:

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one of each clause per ALTER TABLE statement.

Answer 4

I don't believe there is; any statement that worked on rows that didn't satisfy the where clause would update rows you didn't intent to update. Jason's answer is correct, but, I think, a bit unsafe, unless you are really sure that's what you want.

Answer 5

You could do this - repeat as necessary for each column:

UPDATE `table1`  SET
    `col1` = IFNULL(col1, 0),
    `col2` = IFNULL(col2, 0);

Example:

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `col1` int(10) unsigned,
  `col2` int(10) unsigned,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

INSERT INTO `table1` VALUES
(1,    1, NULL),
(2, NULL, NULL),
(3,    2, NULL),
(4, NULL, NULL),
(5,    3,    4),
(6,    5,    6),
(7,    7, NULL);

UPDATE `table1`  SET
    `col1` = IFNULL(col1, 0),
    `col2` = IFNULL(col2, 0);

SELECT * FROM `table1`;

+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    0 |
|  2 |    0 |    0 |
|  3 |    2 |    0 |
|  4 |    0 |    0 |
|  5 |    3 |    4 |
|  6 |    5 |    6 |
|  7 |    7 |    0 |
+----+------+------+

UPDATE

If you want to alter the table structure by changing columns so that they no longer accept nulls, you could do it with a stored procedure. The following stored procedure queries the INFORMATION_SCHEMA COLUMNS for information about columns in a given database table. From that information, it builds up a prepared statement which is then used to alter the table structure. You may need to tweak it to suit your exact requirements - at the moment, it looks for INT columns which do not have NOT NULL set:

delimiter //
DROP PROCEDURE IF EXISTS no_nulls//
CREATE PROCEDURE `no_nulls` (IN param_schema CHAR(255), IN param_table CHAR(255))
BEGIN

    SET @alter_cmd = (SELECT CONCAT(
        'ALTER TABLE ',
        param_table,
        GROUP_CONCAT(
            ' MODIFY COLUMN ',
            `column_name`, ' ',
            `column_type`,
            ' NOT NULL'
            SEPARATOR ', ')
        ) AS `sql_cmd`
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE `table_schema` = param_schema
    AND `table_name` = param_table
    AND LCASE(`data_type`) = 'int'
    AND LCASE(`is_nullable`) = 'yes');

    IF NOT ISNULL(@alter_cmd) THEN
        SELECT @alter_cmd;
        PREPARE stmt FROM @alter_cmd;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

END//
delimiter ;

Example:

CREATE TABLE `test`.`table1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `col1` int(10) unsigned,
  `col2` int(10) unsigned,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CALL no_nulls('test', 'table1');
    +----------------------------------------------------------------------------------------------------------------+
| @alter_cmd                                                                                                     |
+----------------------------------------------------------------------------------------------------------------+
| ALTER TABLE table1 MODIFY COLUMN col1 int(10) unsigned NOT NULL,  MODIFY COLUMN col2 int(10) unsigned NOT NULL |
+----------------------------------------------------------------------------------------------------------------+

SHOW CREATE TABLE `test`.`table1`;

CREATE TABLE `table1` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `col1` int(10) unsigned NOT NULL,
    `col2` int(10) unsigned NOT NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The following line displays the command that is to be executed, and may be removed from the stored procedure if necessary:

SELECT @alter_cmd;