CODEWITHSUNDEEP
mysqlsql
OPR
OPR

Reputation: 29

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

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

Upvotes: 0

Views: 230

Answers (5)

forpas
forpas

Reputation: 164064

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  |

Upvotes: 0

Morteza Zarinderafsh
Morteza Zarinderafsh

Reputation: 1

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");

Upvotes: 0

Webdeveloper_Jelle
Webdeveloper_Jelle

Reputation: 2856

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.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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`);

Upvotes: 1

jared
jared

Reputation: 483

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

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

Upvotes: 2

Related Questions