I am sure I am going to feel ignorant when I get the answer to this question because it will be something simple that I have overlooked, but here goes anyhow.
I am using MySQL Version 3.23.51-max. I am developing an N-tier application using Java on the server side accessing MySQL through JDBC. On one of my tables whenever someone makes a change to the record, what I actually need to do is create a new record that is an exact copy of the existing record and apply the changes to it and set a deactivation date on the original record so that it can later be viewed and we can see when it was active and when it was deactivated. My problem is that I can't seem to put the SQL together to do this. You can't do an "Insert...Into" where the destination table also appears in the "From" clause. What I need is something like "insert into t1 select * from t1 where t1.ID=x". I have looked into the "Select" statement to see if there was a way to do a "Select t1.* into t2 temp from t1 where t1.id=x" to create a temporary table and then do an "insert into t1 select * from t2", but so far I can't see any way of doing this. It could be done in code by doing a select and rounding up all the names of the fields except for the primary key and then creating an "insert" statement using the field names and values, but if there is a simpler and less convoluted way of going about it I would prefer it. Thanks for any help. Darrell --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
