Delete from one table but move the info to another 1

[kupe]

Is there a way to say in MySQL, please,

DELETE * FROM ThisTable INSERT into ThatTable;

 

[TonyGroves]

No. You would have to do something like:

(1) Copy records across:[tt]
INSERT totable
SELECT * FROM fromtable;[/tt]

(2) Delete the original records:[tt]
TRUNCATE fromtable;[/tt]

 

[kupe]

Sorry, Tony, I should have added a where clause.

INSERT totable
SELECT * FROM fromtable WHERE tblID = 203;

Which won't make much difference to your code, except I suppose the second would be

DELETE FROM ... WHERE ...;

I think that's right.(?) Cheers

 

[TonyGroves]

Dead right. Truncate is just a faster way to delete all the records from a table. You might also want to lock the tables if there is any chance of another client jumping in before the transaction is complete.

 

[kupe]

Thanks very much, Tony. (More advice by you filling the notebook.) Cheers