On Thursday, September 26, 2002, at 10:46 AM, Darrell A. Sullivan, II wrote:
> 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. Try this: Create Temporary Table temp_table Select * From t1 Where ID = 'x'; Update temp_table Set ActivationDate = Now() where ID = 'x'; Insert into ti select * from temp_table where ID = 'x'; When I tried this a got a table already exist error on the first line, but the table was created correctly. I also got an error on the third line but again it was entered correctly. -- Clayburn W. Juniel, III -- Effective Software Solutions Phone: (602) 326-7707 Mobile: (602)326-7707 Email: [EMAIL PROTECTED] http://EffectiveSoftwareSolutions.com -- --------------------------------------------------------------------- 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
