• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Insert if not exist

 
Mark Miranda
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table called EMPLOYEES with the columns (id,firstname,lastname)

How can I make sure to only insert when the entry doesn't exist?

currently my query statement is INSERT INTO EMPLOYEES VALUES ('kd123','Teemu','Selanne')

now this will definitely insert the entry regardless if it exist.

 
Paul Clapham
Sheriff
Posts: 20980
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could do a SELECT to look for the record first, and if you don't find then INSERT it.

Or you could put a key constraint in your database, so that trying to add the record a second time would throw an SQLException.

Or you could see if your database has some kind of an "INSERT-OR-UPDATE" statement: some do, I think, but I can't remember any details at the moment.
 
Mark Miranda
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, thanks.
 
Mark Miranda
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found this example:

What's 42, can you just put whatever number you want on there?



src: http://www.dbforums.com/microsoft-sql-server/1640697-insert-where-not-exists.html
 
Paul Clapham
Sheriff
Posts: 20980
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I understand that correctly, the four values in line 13 of your code block correspond to the four column names in line 10. So they should be the data you want to be in those columns in the row you're about to insert with that code.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34218
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Or you could see if your database has some kind of an "INSERT-OR-UPDATE" statement: some do, I think, but I can't remember any details at the moment.

The merge statement sounds like what you are thinking of.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic