CODEWITHSUNDEEP
mysqlsql
kbuilds
kbuilds

Reputation: 1071

SELECT… INSERT with NOT NULL fields

I am trying to do a SELECT... INSERT into a table with constraints that prevent NULL values:

mysql> create table if not exists table1 (
    -> id int not null auto_increment,
    -> description varchar(45),
    -> primary key (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists table2 (
    -> id int not null auto_increment,
    -> description varchar(45) not null,
    -> primary key (`id`),
    -> unique index `unique_desc` (`description`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert ignore into table1
    -> (description)
    -> values("stupid thing"),
    -> ("another thing"),
    -> (null),
    -> ("stupid thing"),
    -> ("last thing");
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+---------------+
| id | description   |
+----+---------------+
|  1 | stupid thing  |
|  2 | another thing |
|  3 | NULL          |
|  4 | stupid thing  |
|  5 | last thing    |
+----+---------------+
5 rows in set (0.00 sec)

Cool, we have the source (table1) and destination (table2) tables created, and the source table populated with some duplicate, null data.

If I do a normal SELECT... INSERT into the destination table, I get a column with empty string as the value:

mysql> insert ignore into table2
    -> (description)
    -> select description
    -> from table1;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 5  Duplicates: 1  Warnings: 1

mysql> select * from table2;
+----+---------------+
| id | description   |
+----+---------------+
|  3 |               |
|  2 | another thing |
|  4 | last thing    |
|  1 | stupid thing  |
+----+---------------+
4 rows in set (0.00 sec)

This is bad. But some boss brogrammer led me to the answer in this question:

MySQL Insert Select - NOT NULL fields

And now this method gives me the desired result:

mysql> insert ignore into table2
    -> (description)
    -> select description
    -> from table1
    -> where description <> '' and description is not null;
Query OK, 3 rows affected (0.00 sec)
Records: 4  Duplicates: 1  Warnings: 0

mysql> select * from table2;
+----+---------------+
| id | description   |
+----+---------------+
|  2 | another thing |
|  3 | last thing    |
|  1 | stupid thing  |
+----+---------------+
3 rows in set (0.00 sec)

Is there a way for me to get the above result without having to manually protect each field using the WHERE clause?

Thanks in advance,

K

Upvotes: 0

Views: 2510

Answers (1)

joemienko
joemienko

Reputation: 2290

This technically answers your question in that you can eliminate the nulls by a join instead of the where clause. 

insert ignore into table2
(description)
select t.description from table1 t
join 
(
select distinct description from table1
) t1 on (t.description=t1.description);

I am pretty sure, however, that you will need to specify a join for each field though. Off the top of my head, I can't think of a way around this.

Upvotes: 1

Related Questions