Inserting data from Mariadb table into another table

Question

I couldn't seem to get the right syntax down in order to insert data from one table into another if main_table had an empty value for email. If the main_table email is empty for an id then I would lke to insert the email for that id from the secondary_table:

MariaDB> SELECT * FROM `main_table`;
+------+---------------------+----------------------------------+------+
| Id   | Email               | Other                            | More |
+------+---------------------+----------------------------------+------+
| 1    | [email protected] | blah                             | A    |
| 2    |                     | needs email from secondary_table | B    |
| 3    | [email protected] | blah                             | C    |
+------+---------------------+----------------------------------+------+
3 rows in set (0.09 sec)

MariaDB> SELECT * FROM `secondary_table`;
+------+---------------------+-------+
| Id   | Email               | Info  |
+------+---------------------+-------+
| 1    | [email protected] | blah  |
| 1    |                     | blank |
| 2    | [email protected] | blah  |
| 2    |                     | blank |
| 3    | [email protected] | blah  |
| 3    |                     | blank |
+------+---------------------+-------+
6 rows in set (0.09 sec)

In this example id number 2 in the main_table has the email empty. I'm trying to get the id with the email from the secondary_table inserted to the main_table. I've tried:

INSERT INTO `main_table`
(`Email`)
VALUES
( SELECT `Email` FROM `secondary_table` WHERE `Id` IN
( SELECT `Id` FROM `main_table` WHERE `Email` == '') ) ;

And various other failures along the way... maybe it's something simple, but I'm stuck!

id in secondary_table is not unique. You need to define which of multiple possible rows you want to use for an update. — Paul Spiegel
– Paul Spiegel, Commented Mar 14, 2017 at 18:48
@PaulSpiegel: I guess that's sort of another question apart from this. I have no idea where to even start on that one, considering that not all Id's have a blank Email value underneath. Thanks for your all your great help, it was tremendous for the immediate issue. — Jack O'Leary
– Jack O'Leary, Commented Mar 14, 2017 at 19:58

ScaisEdge · Accepted Answer · 2017-03-14 18:45:14Z

1

looking to your sample seem that you need updated (with join) and not insert
(assuming that the email field in main_table is null)

update main_table
inner join secondary_table ON main_table.id = secondary_table.id 
set main_table.Email = secondary_table.Email               
where  main_table.Email is null     
and  secondary_table.email <> ''

or assuming hat the email field in main_table is =''

update main_table
inner join secondary_table ON main_table.id = secondary_table.id 
set main_table.Email = secondary_table.Email               
where  main_table.Email = ''
and secondary_table.email <> ''

edited Mar 14, 2017 at 18:45

answered Mar 14, 2017 at 17:50

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

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

7 Comments

Jack O'Leary Over a year ago

I get an error (for both examples) Error : Not unique table/alias: 'main_table'. I'm pretty sure Email = ''. I'm not sure what that error indicates. Id is int(10) and the rest are varchar(255). If you need additional information just let me know - Thanks!

Paul Spiegel Over a year ago

ON is missing

Paul Spiegel Over a year ago

Since the email column in secondary_table can also be blank, you should add the condition and secondary_table.email <> ''

Jack O'Leary Over a year ago

@PaulSpiegel: I had a question about that actually... sometimes the blank Email field contains another email address in the secondary_table, is there a way to specify to use last one that isn't blank? (eg. in secondary_table Id 2 could have [email protected] and in the empty row below could be [email protected]), so the update would use that one. It's not a huge deal if it's too difficult to do that, but I do have a few like that... thanks again :)

ScaisEdge Over a year ago

how do you assume that is the last .. without a proper order ..?

|

Collectives™ on Stack Overflow

Inserting data from Mariadb table into another table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related