File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Insert if not exist Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert if not exist" Watch "Insert if not exist" New topic
Author

Insert if not exist

Mark Miranda
Greenhorn

Joined: Feb 25, 2011
Posts: 24
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Feb 25, 2011
Posts: 24
Ok, thanks.
Mark Miranda
Greenhorn

Joined: Feb 25, 2011
Posts: 24
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
 
subject: Insert if not exist
 
Similar Threads
optimization / right architecture
Problem of my java code with mysql database.Thanks~
Connecting with MYSQL Database
Struts2 newB: some simple questions
How to Prevent Multiple entries stored in database