List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 7 2003 8:56pm
Subject:Re: Can records be moved between tables without knowing columns
View as plain text  
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
	dnelson@stripped
Thread
Can records be moved between tables without knowing columnsJim7 Oct
  • Re: Can records be moved between tables without knowing columnsDan Nelson7 Oct
  • Re: Can records be moved between tables without knowing columnsIllyes Laszlo8 Oct