Mysql insert into with multiple id from another table

Question

I have 2 table, first I retrieve all Ids like this:

select DISTINCT entity_id From table1

I want to create a bulk insert using entity_id and prevent duplicate if the entity_id have a specifique value in the colomn2:

example

 ********************************************* * ID * ENTITY_ID * COLOMN2 * value * ********************************************* * 1 * 230 * 20 * 1 * * 2 * 230 * 100 * 1 * * 3 * 280 * 20 * 0 * * 4 * 220 * 20 * 1 * ********************************************* select DISTINCT entity_id From table1 // return : 230,280,220 INSERT into table1 ('ENTITY_ID','COLOMN2','VALUE') VALUES([the select id],100,1) WHERE COLOMN2<>100

or something like that?

the result need to be:

creating 2 insert... entity_id=220 and entity_id=280

with the colomn2=100 and value = 1

any idea ?

use a subquery in the second part stackoverflow.com/questions/20232947/… — Sam
– Sam, Commented Dec 20, 2013 at 17:32
I think some information is missing here... You select unique entitity_ID's and then insert them into table1... based off of what data? from where? Not sure where the data for this decision comes from. Also "Colomn2" and "value" are terrible names. — Digital Chris
– Digital Chris, Commented Dec 20, 2013 at 17:38

Sam · Accepted Answer · 2013-12-20 17:46:13Z

2

Use a subquery

INSERT INTO table1 ('ENTITY_ID','COLOMN2','VALUE') (select DISTINCT entity_id ,100,1 FROM table2 WHERE column2<>100)

edited Dec 20, 2013 at 17:46

answered Dec 20, 2013 at 17:37

Sam

2,7613 gold badges22 silver badges30 bronze badges

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

2 Comments

Jean-philippe Emond Over a year ago

it's a good part of solution.. but I guess that we'll do a duplicate entity_id where column2 =100?

Sam Over a year ago

no because it's a distinct entity_id. or you can add WHERE column2<>100 to make sure

Brian A. Henning · Accepted Answer · 2013-12-20 17:44:01Z

If I'm understanding you correctly, you consider "duplicate" to mean BOTH "ENTITY_ID" and "COLOMN2" have the same value, i.e:

(50, 50) & (50, 99) == not duplicate

(80, 42) & (99, 42) == not duplicate

(55, 66) & (55, 66) == duplicate

Can you create a compound unique key on your database? Such as, for mysql:

CREATE UNIQUE INDEX nodupes ON table1 (ENTITY_ID, COLOMN2);

You would then need to determine how to handle errors during the insert process.

Collectives™ on Stack Overflow

Mysql insert into with multiple id from another table

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related