Move from one table to another

问题:

I need to move some data from a table to a archive table.

Now I do it this way:

$sql = 'INSERT INTO history_products 
         SELECT * FROM products WHERE category_id='.$id;

And then I delete data from products table.

$sql_delete = 'DELETE FROM products WHERE category_id = '.$id;

Each product have a unique id. But I want to make this in a secure way, now even if a row isn't inserted in history_products the row will be deleted from products table. I don't want to delete if insert fails.

回答1:

I expected you have a field called id (ProduktID), which products must have the same id in both tables then, the following should work.

Delete from products where products.id IN (Select history_products.id from history_products)

回答2:

If using unique keys, delete all records from products that have the same keys as those in the history table.

回答3:

DELETE FROM products WHERE category_id IN (SELECT category_id FROM history_products)

This'd only delete records in the products table where a corresponding record exists in history_products.