my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Insert with a select on the same table. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Insert with a select on the same table." Watch "Insert with a select on the same table." New topic

Insert with a select on the same table.

A knibbs
Ranch Hand

Joined: Aug 23, 2006
Posts: 158
Hi all I am trying to use a db connection using jdbc in order to insert a record into a table. The main issue is that I need to do a select on that same table first in order to find out what the last number was put in, as the record I am inserting is that number + 1.

I have the following, which I think is fairly close, but i'm not quite certain of the intricacies(sp) of how jdbc would convert this. I appreciate any comments or suggestions that anyone may have.

+ " ( ?, 'Z', ?, " +
" (Select MAX(table.ord-item) from table where table.entity ='"+Constants.ENTITY+ "' AND table.ORD-NUM=" +100 +
" AND table.ORD-TYPE='ORD' AND table.BRANCH='006' )" +

I am getting the error of unable to understand after Insert. Again any thoughts or suggestions would be greatly appreciated.
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

Well, I expect that is invalid SQL when all the dust settles and that string is evaluated. You might consider doing the SELECT first and the INSERT as a separate statement. Or if your database supports auto-numbering columns or some similar concept, then just make use of that.

To use auto-numbering, declare the column that is going to store the sequence number appropriately (varies by database). Then INSERT all the columns except that one, and the database will assign the default automatic number to it.
A knibbs
Ranch Hand

Joined: Aug 23, 2006
Posts: 158
I wish I could use automatic numbering, but unfortunately that isn't an option in this case. I was initially doing the select and then passing that value in as an argument, but there was 2 things I was worried about
1) The time in between the select occuring and the insert occuring someone else may be working on that table.
2) I am also trying to keep both database access as well as code to a minimum.
I agree. Here's the link:
subject: Insert with a select on the same table.
It's not a secret anymore!