aspose file tools*
The moose likes JSP and the fly likes SQL Insert and Delete at same time? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "SQL Insert and Delete at same time?" Watch "SQL Insert and Delete at same time?" New topic

SQL Insert and Delete at same time?

Zac Roberts
Ranch Hand

Joined: Jan 29, 2002
Posts: 82
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?

Zac Roberts<br />SCJP2
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 60048

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.

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Zac Roberts
Ranch Hand

Joined: Jan 29, 2002
Posts: 82
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...
I agree. Here's the link:
subject: SQL Insert and Delete at same time?
Similar Threads
pass scjd, graded in 4 days.
Concurrency Issue
few questions about design struts application
File Process Fails but Generated No Exception
RandomAccessFile & Threading