update all NULL fields MySQL

Question

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.

Answer 1

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;