在單個操作中插入和刪除（從一個表移動到另一個表）

出於這個問題，我將使用該複雜排序的結果來決定將哪些行的數據從一個表移動到另一個表。雖然我還沒有嘗試過，但我認為通過以下方式進行“複製”部分應該沒有問題：

INSERT * FROM (...)

SELECT在括號內使用我的結果。

但是，如果我隨後使用相同的查詢來嘗試SELECT相同的結果以執行 a DELETE FROM，則結果將不再包括那些相同的行。所以，我需要一些方法來儲存至少主鍵（和源表，因為我們正在談論 2 個源表和一個目標表），以便DELETE從正確的表中找到正確的行。

根據我的閱讀和理解，不可能在單個查詢中執行，並且無論如何都需要事務（我需要查找如何正確執行）。

所以我的第一種方法是先進行INSERT複制，然後可能從這兩個表中刪除包含出現在我的目標表中的主鍵的任何行。這似乎有點低效，因為該表將比兩個原始表大得多，而且我認為從我從子查詢中獲得的派生表中，使用位於那裡的資訊會更有效率。那麼，關於我如何最好地解決這個問題的任何線索？

首先將所有內容插入臨時表。然後，您的插入和刪除只是臨時表中的所有內容，而不是您複雜的查詢。這解決了你如何做兩半的問題，並且可能會比多次執行複雜的查詢更好。

我做的 MS-SQL 比 My-SQL 多，所以我將連結到 Stack Overflow 上的另一個答案。

https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table

在 MS-SQL 上，它很簡單

 SELECT .... into #MyTempTable FROM ... WHERE..

您可以使用觸發器插入table2已從table1.

CREATE TABLE table1(id int, name varchar(10), level int);
INSERT INTO table1(id, name, level) VALUES
 (0, 'a', 0)
 , (1, 'b', 1)
 , (2, 'c', 0)
 , (3, 'd', 2)
 , (4, 'e', 1)
 , (4, 'f', 0);

CREATE TABLE table2(id int, name varchar(10), level int);

DELIMITER //

CREATE TRIGGER table1_after_delete
   AFTER DELETE
   ON table1 FOR EACH ROW        
   trig: BEGIN
   IF (@TRIGGER_AFTER_DELETE_ENABLE = FALSE)
   THEN
       LEAVE trig;
   END IF;
   INSERT INTO table2
   ( 
       id
       , name
       , level
   )
   VALUES
   ( 
       OLD.id
       , OLD.name
       , OLD.level 
   );
END; //

DELIMITER ;

DELETE FROM table1 WHERE level = 1;
SET @TRIGGER_AFTER_DELETE_ENABLE = FALSE;
DELETE FROM table1 WHERE level = 2;

此範例刪除table1wherelevel=1和 where 中的所有內容level=2。table2僅當 時才將其移動到@TRIGGER_AFTER_DELETE_ENABLE <> FALSE。請參閱範例sqlfiddle。

所以：

• 帶有 1 的行被插入到 table2 中（變數尚未設置）
• 帶有 2 的行不會插入到 table2 中（變數設置為 FALSE）

表 1 的輸出：

d   name    level
0   a       0
2   c       0
4   f       0

表2的輸出：

id  name    level
1   b       1
4   e       1

Row withlevel=2不再存在（參見範例sqlfiddle）。

你基本上是這樣選擇的：

• @TRIGGER_AFTER_DELETE_ENABLE = FALSE在刪除語句之前 => 刪除和複製數據
• @TRIGGER_AFTER_DELETE_ENABLE <> FALSE=> 只刪除數據

SQL 伺服器

雖然問題與此 RDBMS 無​​關，但對於 SQL Server，我寧願使用以下OUTPUT子句：

DELETE FROM t
OUTPUT deleted.id, deleted.name, deleted.level INTO table2
FROM table1 t
WHERE level > 0;

它只是將剛剛刪除的內容的輸出插入到其他地方。不需要臨時表，IO 將被限制在tempdb.

引用自：https://dba.stackexchange.com/questions/143637