Insert into mysql table only if those values are not in table

Question

I have search already an answer but i can't find one that is good for my situation. I have a table called Names like this

ID NAME   Age 
1  Paula   20 
2  Mark    17

And i want to run this sql

Insert into table names(name,age) values ("Chriss",15)//should be inserted 

Insert into table names(name,age) values ("Mark",17)// should be ignored 

Insert into table names(name,age) values ("Andrea",20) //should be inserted

So how can I ignore second insert query

> https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist — ashirkhan p m, Dec 09 '19 at 11:01

score 2 · Answer 1 · answered Dec 09 '19 at 10:59

2

Create a constraint that demands NAME and Age to be unique in the table.

ALTER TABLE `tablename` ADD UNIQUE `unique_index`(`NAME`, `Age`);

answered Dec 09 '19 at 10:59

jared

473
3
16

score 1 · Answer 2 · answered Dec 09 '19 at 11:02

1

You would either need to Add UNIQUE constraint or check the data at the run time (if you don't have a permission to change table schema):

ALTER TABLE `Table_name` 
     ADD UNIQUE INDEX (`NAME`, `AGE`);

answered Dec 09 '19 at 11:02

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 3 · answered Dec 09 '19 at 11:03

0

You can use:

INSERT INTO names(name,age)
SELECT * FROM (SELECT 'Chriss', 15) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM names WHERE name = 'Chriss' AND age = 15
) LIMIT 1;

An other way is just make the columns name and age UNIQUE so the query fails.

answered Dec 09 '19 at 11:03

Webdeveloper_Jelle

2,868
4
29
55

score 0 · Answer 4 · edited Dec 09 '19 at 11:31

0

Change your query to this:

Insert into table names(name,age) 
   SELECT "Chriss",15 WHERE NOT EXISTS (SELECT 1 FROM names WHERE `name` = "Chriss");

Insert into table names(name,age) 
   SELECT "Mark",17 WHERE NOT EXISTS (SELECT 1 FROM names WHERE `name` = "Mark");

Insert into table names(name,age) 
   SELECT "Andrea",20 WHERE NOT EXISTS (SELECT 1 FROM names WHERE `name` = "Andrea");

edited Dec 09 '19 at 11:31

GameO7er

2,028
1
18
33

answered Dec 09 '19 at 11:11

Morteza Zarinderafsh

1
1

forpas · Answer 5 · 2019-12-09T11:57:28.493

First create a unique constraint for the columns NAME and Age:

ALTER TABLE names ADD UNIQUE un_name_age (`NAME`, `Age`);

and then use INSERT IGNORE to insert the rows:

Insert ignore into names(name,age) values 
  ("Chriss",15),
  ("Mark",17),
  ("Andrea",20);

So if you try to insert a duplicate name the error will just be ignored and the statement will continue with the next row to insert.
See the demo.
Result:

| ID  | NAME   | Age |
| --- | ------ | --- |
| 1   | Paula  | 20  |
| 2   | Mark   | 17  |
| 3   | Chriss | 15  |
| 4   | Andrea | 20  |

Insert into mysql table only if those values are not in table

5 Answers5