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

Question

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?

Are you able to modify the "not null" fields in table1 to allow for nulls? — Zack Macomber
– Zack Macomber, Commented Jan 4, 2012 at 16:06
@ZackMacomber that wouldn't make sense, if the fields would allow null values, the constraints wouldn't be there in the first place. Unless the OP cannot control the first table ofcourse, but we lack such information — thomaux
– thomaux, Commented Jan 4, 2012 at 16:08
You can't put null values in columns specified as not null, that's kind of the point. You have to either change the constraints to allow nulls, or come up with valid values from somewhere. If you want, you can use a join on the select to pull them from somewhere else, that works fine too. — PlexQ
– PlexQ, Commented Jan 4, 2012 at 16:08
My comment on modifying the "not null" fields was along the lines of changing the constraints to allow for nulls...sorry for the lack of clarity. I have had to remove "not null" constraints on table fields in the past so thought that may be an option here... — Zack Macomber
– Zack Macomber, Commented Jan 4, 2012 at 16:17

a sad dude · Accepted Answer · 2012-01-04 16:14:01Z

1

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

answered Jan 4, 2012 at 16:14

a sad dude

2,79020 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adrian J. Moreno Over a year ago

Also, you don't want to save a password as plain text, you should use "SELECT firstname, lastname, PASSWORD('password1'), email FROM table2". dev.mysql.com/doc/refman/5.0/en/…

tom_ireland Over a year ago

Wow! Thanks for the prompt response guys, that was quick. Apologies if I didn't clarify but I do need to populate the not null fields in table one with a value but the way I was thinking it need doing was to create all these additionals fields in table 2 so they could be selected in my query. Thanks @a_sad_dude your response appears to work for me...and no additional table fields in table 2!!!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related