mysql query insert where not exists

Question

I search a way to enter datas in database(MySQL),if they dont exist yet in DB:something like:

 INSERT INTO TABLE(id,name,surname) VALUES('','name1','surname1')
 WHERE NOT EXISTS (SELECT * FROM TABLE)

What wrong with this query ?

Answer 1

While you can't use NOT EXISTS with your above query, you can with a subquery. Here's one way to do it using a LEFT JOIN and a subquery:

INSERT INTO yourtable (name, surname) 
SELECT a.name, a.surname
FROM  
  (SELECT 'name1' name,'surname1' surname) a 
  LEFT JOIN yourtable b ON 
      a.name = b.name AND a.surname = b.surname
WHERE b.name IS NULL;

SQL Fiddle Demo

And the NOT EXISTS approach:

INSERT INTO yourtable (name, surname) 
SELECT a.name, a.surname
FROM  
  (SELECT 'name1' name,'surname1' surname) a 
WHERE NOT EXISTS (
       SELECT 1 
       FROM yourtable 
       WHERE name = 'name1' and surname = 'surname1')

More Fiddle

Answer 2

There's a recommendation of doing your homework before asking. If you look in google or in stackoverflow, you can find a similar question to yours:

MySQL: Insert record if not exists in table

I believe that would solve your issue.

To be clear: No, you cannot use WHERE in an INSERT. But YES, you can lock tables to avoid inserting records where they exists. That is called a CONSTRAINT and needs to be declared from the table creation.

Everything you need is in the linked question.

Answer 3

Lets say you have tables defined as follows:

+------+  +------+
|Names |  |Add   |
+------+  +------+
|ID    |  |ID    |
|FName |  |FName |
|LName |  |LName |
+------+  +------+

If you wanted to insert the Add table into the Names table without adding duplicates you could use the following select statement to create a line for each insert statement you need. This also gives the extra benefit of creating an audit trail.

Select CONCAT('INSERT INTO Names \(ID, FName, LName\) VALUES \(',a.ID,',',a.FName,',',a.LName,'\)')
From Add a LEFT JOIN Names N on a.ID = n.ID AND a.FName = n.FName AND a.LName = n.FName
Where n.ID is null;

I'm not sure I have the syntax perfect as I don't use mySQL, but it should be in the ballpark.