Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Insert with a select on the same table.

 
A knibbs
Ranch Hand
Posts: 158
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


"INSERT INTO table (PRODUCT, ITEM-STATUS, PRICE, ORD-ITEM) VALUES "
+ " ( ?, '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
Sheriff
Pie
Posts: 20758
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 158
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic