multiple insert row if not exists (based on multiple columns)

Question

Imagine an existing table with data (names are self explanatory):

id,name,telephone_1,telephone_2

i want to insert multiple new records with one INSERT (hopefully multiple new records with one insert -if thats a problem i could fall back to insert one-by-one), but i want a new row to be added ONLY if the telephone_1 OR the telephone_2 of the new record does NOT already exist either in telephone_1 OR the telehpone_2 of an existing record.

That means if we have existing data

1, Jimmy, 123, 456

i should not be able to add a

NULL,John,444,123

because John's telephone_2 exists already as telephone_1 of Jimmy.

I'm planning to let mysql do the heavy work instead of my program, but how can i do that? I have done something similar in the past using UNIQUE indexes, but the problem in this case is that the columns are 2. The telephone_1 should not collide with telephone_1 and also should not collide with telephone_2 (and same for telephone_2)

UPDATE-CLARIFICATION: The data i'm trying to insert are not from another table; they are generated on the fly from my program.

Rimas · Accepted Answer · 2014-09-02 14:39:06Z

1

Suppose your table name is contacts, then try to insert record:

INSERT INTO contacts(name,telephone_1,telephone_2)
 SELECT 'John', 444, 123
 FROM contacts
 WHERE telephone_1 NOT IN (444, 123)
 AND telephone_2 NOT IN (444, 123)
 LIMIT 1;

Trick there: if WHERE condition is false then SELECT returns no rows and record is not inserted.

Try here: http://sqlfiddle.com/#!2/5a6e0/1

edited Sep 2, 2014 at 14:39

answered Sep 2, 2014 at 10:28

Rimas

6,0332 gold badges28 silver badges40 bronze badges

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

1 Comment

Rimas Over a year ago

@Sharky: small correction added LIMIT 1; to query

Ronak Shah · Accepted Answer · 2014-09-02 09:49:01Z

0

Try this:

Insert into tableA select * from tableB where tableB.telephone_1 not in (select telephone_1 tableA union select telephone_2 tableA ) AND tableB.telephone_2 not in (select telephone_1 tableA union select telephone_2 tableA )

where

tableA is your main table where you want to insert new record. tableB is other table from where you want to fetch record

answered Sep 2, 2014 at 9:49

Ronak Shah

1,5592 gold badges13 silver badges20 bronze badges

5 Comments

Sharky Over a year ago

there is no TableB, im getting data from my application.

sri Over a year ago

@Sharky You may need to have a temporary table. Or it seems like you need some procedure.

Sharky Over a year ago

@sri thank you for your answer, are subqueries the only way? is there something more elegant?

Ronak Shah Over a year ago

@Sharky You need to create temporary table in MySQL when you need to get data from your application. shri is right

Ronak Shah Over a year ago

@sharky Sub query is fast. You can use join also but I suggest use this sub query

Collectives™ on Stack Overflow

multiple insert row if not exists (based on multiple columns)

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related