CODEWITHSUNDEEP
mysql
Sharky
Sharky

Reputation: 6274

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

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.

Upvotes: 0

Views: 1089

Answers (2)

Rimas
Rimas

Reputation: 6024

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

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

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

Upvotes: 0

Related Questions