Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

MySQL Insert if does not exist

 
Paul Peterson
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there a straightforward way write something like so that it only inserts the data if it doesn't already exist?
 
Brian Tkatch
Bartender
Posts: 568
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Peterson wrote:Is there a straightforward way write something like so that it only inserts the data if it doesn't already exist?

Add a WHERE NOT EXISTS clause.
 
Paul Peterson
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank You!!
 
Hardik Jani
Greenhorn
Posts: 7
Android AngularJS PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Below is the example, Through help of this example you can solve your problem.





-- Now, insert a record




Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0



+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+


Hope, it can useful to you.
 
Dave Tolls
Ranch Hand
Posts: 2365
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not just make regionName UNIQUE?
 
Brian Tkatch
Bartender
Posts: 568
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hardik Jani wrote:INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;


Please use the code tags when posting code. It makes it so much easier to read.

Anyway, that statement is redundant. Here's a cleaner way of writing the same thing:

 
Brian Tkatch
Bartender
Posts: 568
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Why not just make regionName UNIQUE?

Maybe it is UNIQUE already. INSERT WHERE NOT EXISTS avoids errors, and, as a bonus, is somewhat self-documenting.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!