从一个表移动到另一个表

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.

然后我从products表中删除数据。

$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.

每个产品都有一个唯一的ID。但我希望以安全的方式进行此操作,现在即使在history_products中未插入行,该行也将从products表中删除。如果插入失败,我不想删除。

3 个解决方案

#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.

我希望你有一个名为id(ProduktID)的字段,两个表中的产品必须具有相同的id,以下内容应该有效。

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

从products.id IN的产品中删除(从history_products中选择history_products.id)

#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.

这只会删除产品表中history_products中存在相应记录的记录。

#1