MySql Multiple insert with select

Question

I am trying to insert multiple rows with select, something like this:

INSERT INTO Check( name, price, count, order_id) SELECT p.name, p.price, :count, :order FROM Product p WHERE p.id IN(:id1, :id2, :id3)

The problem is that every :id1, :id2, ... has own :coun1, :count2, :count3.

:id is the product id, :count is the 'count' of this product. I want to make a basket. But the data will be too big. So i want to optimize it, and insert in one query... — Urmat
– Urmat, Commented Jul 17, 2014 at 5:28

Jason Heo · Accepted Answer · 2014-07-17 05:22:25Z

1

Are you looking for something like this?:

INSERT INTO Check( name, price, count, order_id) SELECT p.name, p.price, IF (id = :id1, :count1, IF (id = :id2, :count2, :count3)), :order FROM Product p WHERE p.id IN(:id1, :id2, :id3)

answered Jul 17, 2014 at 5:22

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

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

3 Comments

Urmat Over a year ago

Thank you, but i think it would be too many "if". Presumably about 100 "if"

Jason Heo Over a year ago

@Urmat Well, How about inserting 100 count records into new CountTbl and join with Product?

Urmat Over a year ago

I think this is genius. Insted of hundreds of queries it would be about 3-4 queries.

Rahul · Accepted Answer · 2014-07-17 05:44:05Z

Another option would be .. create a separate table with those variable id and count like

create table tab1(id int, count int); insert into tab1(id,count) values(:id1, :coun1);

Then you can try like

INSERT INTO `Check`( name, price, count, order_id) SELECT p.name, p.price, t.id, t.count FROM Product p JOIN tab1 t on p.id = t.id

Collectives™ on Stack Overflow

MySql Multiple insert with select

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related