Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Insert and Delete at same time?

 
Zac Roberts
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would like to delete a record from table_one and at the same time send it to table_two which funtions as an archive for deleted records. Currently the delete function works fine. I am using a prepared statement. Anyone know how to do this?
Thanks
Zac
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64970
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The JDBC forum would be a more appropriate place for this question, but I'll give it a shot.
There is no single SQL statement that will delete a row from one table and insert it into another. However, if your database supports transactions, you can use a transaction to contain both the insert and delete statement. By doing so, the two statements are treated as an atomic operation where both statements succeed, or neither takes effect.
hth,
bear
 
Zac Roberts
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Bear, unfortunately I am using Microsoft Access becuase this is a very small in house JSP Database application (only 5 users max). I don't think Access supports anything like this but maybe I am wrong. I suppose I could try to add some code to my method (contained in a bean) that contains the delete statement but it would become very messy. Hmmm...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic