multiple values insert join in mysql

Question

I have a query submitting multiple items in table a.

For example:

insert into a values(id,name) (5,'john'),(6,'smith');

Though I also need to select some third value from other table with this id.

For example:

insert into a values(id,name,money) (5,'john',(select money from b where id=5)),(6,'smith',(select money from b where id=6));

The problem with the above is that it's a bit repetitive and also uses sub selects.

I wonder if it's possible to rewrite this using JOIN, (which should also reassure that there is a relation to the table b on that given id, lest it inserts a NULL).

Any ideas?

Phil Birnie · Accepted Answer · 2018-05-30 02:59:04Z

4

You're allowed only one SELECT for each INSERT so you need to re-write this to select multiple rows, not insert multiple values at once. Could you create a temporary table with the two sets of values in it and INSERT those with a JOIN?

CREATE TEMPORARY TABLE _tmp_a (id INT PRIMARY KEY, name VARCHAR(255));
INSERT INTO _tmp_a (5, 'john'), (6, 'smith')
INSERT INTO a (id, name, money) SELECT _tmp_a.id, _tmp_a.name, b.money FROM _tmp_a LEFT JOIN b ON b.id=_tmp_a.id

edited May 30, 2018 at 2:59

Phil Birnie

1,1342 gold badges13 silver badges30 bronze badges

answered Sep 7, 2012 at 0:46

tadman

212k23 gold badges237 silver badges266 bronze badges

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

2 Comments

Anonymous Over a year ago

Well why not! This above looks great, I'm going to try it out. I also wonder if it's possible to replace left join to join, so it wouldn't insert the rows with money equal to null. I'll check it out as well :)

tadman Over a year ago

You can use RIGHT JOIN if you want to skip those entries without corresponding values on the "right" side.

Collectives™ on Stack Overflow

multiple values insert join in mysql

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