在 INSERT 之前刪除數據

我有這張表，用於生成第二個表，結果如下：

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
       COUNT(*) count,
       SUM(amount) volume,
       DATE(created_at) date,
       YEAR(created_at) year,
       MONTH(created_at) month,
       WEEK(created_at) week,
       DAY(created_at) day,
       type transaction_type,
       contract_id, merchant_id, terminal_id,
       status, card_brand, currency
       FROM payment_transactions
       WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
       GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

但是我需要在插入之前刪除舊數據。有沒有一種 SQL 方法可以先刪除從某個日期開始的表數據，然後再插入一個新數據？

是的，您可以DELETE在您的INSERT. 如果您在事務中執行此操作，它們本質上將是一個操作，它們將一起失敗或成功（假設表使用事務引擎，如 InnoDB）：

START TRANSACTION ;

   DELETE FROM
       payment_transactions_daily_facts
   WHERE
       date BETWEEN '2018-11-11' AND '2018-11-14' ;

   INSERT INTO
       payment_transactions_daily_facts
       (count, volume, date, ...)
   SELECT
        ...  ;

COMMIT ;

使用儲存過程。這是 MySQL 儲存過程的教程http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

或者，Google“MySQL 儲存過程”以獲取大量其他讀數。

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