CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Ruairi McGowan
Ruairi McGowan

Reputation: 23

SQL - Using INSERT INTO with SELECT DISTINCT resulting in NULL values being imported

I have what should be a relatively simple problem that I need a solution to. I am beginner to SQL and would greatly appreciate any assistance.

I have been trying to import all the unique Strings from one column (bond) in my 'customer' table, to a new column that I've created in my 'bonds' table.

I have been using the following code:

INSERT INTO bonds(bond_customer_name) SELECT DISTINCT bond FROM customer;

While the SELECT DISTINCT on its own selects the correct 25 records, 1500+ are being imported when I run the above query, with a large proportion of the fields being NULL.

The new bond_customer_name column also has the same data type as the original bond column (VARCHAR(16)).

Upvotes: 1

Views: 3707

Answers (1)

Ruairi McGowan
Ruairi McGowan

Reputation: 23

The problem was that I was creating a list of NULL values upon adding a column for bond_customer_name. Instead, I created a column, name, upon creation of the table, using the following code: 

CREATE TABLE 'bonds' ('id' int(11) NOT NULL AUTO_INCREMENT,'name' varchar(255) DEFAULT NULL, PRIMARY KEY('id')) DEFAULT CHARSET = UTF8;

I then used the following code to successfully import the unique Strings present in Customer.bond into Bonds.name, adding a conditional WHERE:

INSERT INTO bonds(name) SELECT DISTINCT bond FROM customer WHERE bond IS NOT NULL;

This has given me the desired result, thanks to all who helped!

Upvotes: 1

Related Questions