0

I have this table which is used to generate second table with the result:

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

But I need to delete the old data before I make insert. Is there a way with one SQL to delete first the table data starting from some date and them to insert a new one?

4
  • No. Two separate queries only.
    – Akina
    Feb 7, 2019 at 14:00
  • If each INSERT only had to replace one single row, you could use REPLACE.
    – dbdemon
    Feb 7, 2019 at 14:15
  • Can you show me working example from my code please? Feb 7, 2019 at 14:19
  • Search for IODKU.
    – Rick James
    Feb 8, 2019 at 22:45

2 Answers 2

5

Yes, you can issue a DELETE statement before your INSERT. If you do it inside a transaction, they will be essentially one operation, they will either fail or succeed together (assuming that the table uses a transactional engine, like 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 ;
-1

Use stored procedure. Here is a tutorial for MySQL stored procedures http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Alternatively google "MySQL stored procedures" for plenty of other readings.

3
  • Is there a solution without stored procedure? Feb 7, 2019 at 13:31
  • as far as I know the answer is no. but you can execute multiple sql statements (without stored procedures) by seperating the statements with semi-colon ; (thats what i do in SQL Server, guess its the same in mysql). PL SQL (oracle) uses forward slash (/). For example: "delete from products where id=1; insert into select * from inventory;" would execute the delete first then select Feb 7, 2019 at 13:40
  • 1
    You still need use a transaction, even if you write a stored procedure to do this.
    – dbdemon
    Feb 7, 2019 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.