Mysql: insert record if not exists already

Question

0.00/5 (No votes)

See more:

Hi all, i have visual studio 2012 winform c#

Using Mysql, i would like to insert record if not exists already in the table

What I have tried:

INSERT INTO refreshcity (Id, Society) values ('900000','toto')
WHERE NOT EXISTS (Select Id, Society From refreshcity WHERE Id ='900000')

Posted 6-Apr-16 9:53am

Armel_Djient

Updated 13-Jun-19 18:23pm

Add a Solution

4 solutions

Solution 4

INSERT INTO `refreshcity` (`Id`, `Society`) 
SELECT '900000','toto'
WHERE NOT EXISTS (Select `Id` From `refreshcity` WHERE `Id` ='900000') LIMIT 1;

This work for me.

Posted 13-Jun-19 18:23pm

h@ndy

Solution 3

if you have an "unique" column you could simply do "insert ignore into... "; it will automatically check the unique column for duplicate an each row that is about to be inserted and will simply ignore an entry if already existing.

Posted 15-May-18 0:40am

Shaa Dea

Comments

CHill60 15-May-18 7:02am

"ignore into ..." - As discussed on the link provided in Solution 1 a year ago.

Solution 1

Read this thread,
MySQL: Insert record if not exists in table - Stack Overflow[^]

Posted 6-Apr-16 19:39pm

VR Karthikeyan

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

koolprasad2003 · Accepted Answer · 2016-04-06T20:08:00

Solution 2

There is slight change in Query, Just try to select the record first which you want to insert in database, if it is not exist then you can insert in it.
see below snippet

SQL

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'name1', 'add', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'name1'
) LIMIT 1;

Hope it helps

Posted 6-Apr-16 20:08pm

koolprasad2003

Comments

koolprasad2003 7-Apr-16 3:46am

look, I have a table named table_listnames and I want to insert name, address and telephone number in table but before insertion I want to check if the same entry with same name is already exist or not. so first I will select name from table where name is the same name I want to insert. if it is not exist then it will insert new record.
Its for MySQL not for SQL

Armel_Djient 7-Apr-16 3:49am

i got how this command works.
INSERT INTO table_listnames (name, address, tele)
represents the columns where the data will be inserted
SELECT * FROM (SELECT 'name1', 'add', '022') AS tmp
traduces the values to be inserted in the columns(name, address, tele) mentionned above
WHERE NOT EXISTS
to checks if the record already exists or not

Thank you very much for your reply

koolprasad2003 7-Apr-16 5:11am

welcome

klmediadev 17-Oct-19 5:54am

awesome, koolprasadd! thank you for this useful snipp :-) good health!

koolprasad2003 17-Oct-19 6:17am

glad it helps...

Mysql: insert record if not exists already

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Mysql: insert record if not exists already

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us