是否可以使用一条SQL语句将记录从一个表移动到另一个表? [英] Is it possible to move a record from one table to another using a single SQL statement?

问题描述

我需要一个查询来将记录从一个表移动到另一个表而无需使用多条语句吗?

I need a query to move a record from one table to another without using multiple statements?

推荐答案

如果您 真的 想要在单个SQL语句中执行此操作，则可以通过一种方法来完成此操作是在源表上创建一个删除后"触发器，该触发器将行插入到目标表中.这样，您只需将行从源表中删除就可以将其从源表移至目标表.当然，仅当您要插入目标表以在源表上进行 每 删除时，这才起作用.

If you really want to do this in a single SQL statement, one way to accomplish this would be to create an "after delete" trigger on the source table that inserts the row into the target table. This way you can move the row from the source table to the target table simply by deleting it from the source table. Of course this will only work if you want to insert into target table for every delete on the source table.

DELIMITER $$

DROP TRIGGER IF EXISTS TR_A_DEL_SOURCE_TABLE $$

CREATE TRIGGER TR_A_DEL_SOURCE_TABLE AFTER DELETE ON SOURCE_TABLE FOR EACH ROW BEGIN

  INSERT IGNORE INTO TARGET_TABLE(id,val1,val2) VALUES(old.id,old.va1,old.val2);

END $$

DELIMITER ;

因此要将ID为42的行从源表移动到目标表:

So to move the row with id 42 from source table to target table:

delete from source_table where id = 42;

这篇关于是否可以使用一条SQL语句将记录从一个表移动到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！