In the last episode (Oct 07), Jim said:
> I need to be able to 'delete' records from table(s) but still
> maintain their information for an audit trail.
>
> I'd rather not have a field for a deleted flag and have to condition
> all the queries on the table on the state of this flag.
>
> Is it possible to move a record from one table to another easily?
>
> Even better, can I do it opaquely (without knowledge of the columns)?
> Something
> like:
> "move from LiveUsers to DeletedUsers where ID=?;"
> where all the fields in the first table get moved to the corresponding field
> of the 2nd. (If this is possible, what happens with autoincrement,
> timestamp fields?)
You would have to do it as two queries (if you're using BDB or InnoDB
you could do it as one transaction so it's an atomic operation):
insert into DeletedUsers select * from LiveUsers where ID = :id
delete from LiveUsers where ID = :id
The autoincrement and timestamp fields will carry over whatever values
you had in the original table, since you are implicitly selecting and
inserting them with the all-fields "*" wildcard.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
