CODEWITHSUNDEEP
phpmysql
tom_ireland
tom_ireland

Reputation: 3

MySQL insert from another table where field(s) are not null

I am trying to insert data into a table (table1) based on another (table2), the only problem is that table1 contains fields set to not allow null values. Do I need to create these fields in table2 where I am pulling the data from and populate them with a value?

Example not null field: password

If I do not include this in my query then I get an error and the record is not inserted however if I create the field in table2 then insert into my query it works fine. This seems a bit out of the ordinary. Example query below:

Example 1 (no password field in table 2):

$insert_new_records_query = "INSERT INTO table1 (first_name, last_name, email_address) ". 
"SELECT firstname, lastname, email FROM table2";

This generates an error saying that I must include the password field.

Example 2 (password field in table 2):

$insert_new_records_query = "INSERT INTO table1 (first_name, last_name, password, 
email_address) ". 
"SELECT firstname, lastname, password = 'password1', email FROM table2";

This allows the record to be created. The problem is that I have many more fields that are not null in table 1 and I don't think I need to create them in table 2 as blank fields and insert them into my query with values just to create a record. Is there a better way to do this?

Upvotes: 0

Views: 271

Answers (1)

a sad dude
a sad dude

Reputation: 2825

You don't have to create fields, you can simply 'select' default values. Try this instead:

INSERT INTO table1 (first_name, last_name, password, email_address)
    SELECT firstname, lastname, 'password1', email FROM table2

Upvotes: 1

Related Questions