从一张桌子移到另一张桌子 [英] Move from one table to another

问题描述

我需要将一些数据从表移至存档表.

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

现在我这样做:

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

每个产品都有唯一的ID. 但是我现在想以一种安全的方式做到这一点，即使在history_products中未插入任何行，该行也将从products表中删除.如果插入失败，我不想删除.

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(ProduktID)的字段，然后这两个表中的产品都必须具有相同的id，下面的方法应该起作用.

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

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

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

这篇关于从一张桌子移到另一张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！