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?

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

  • Is there a solution without stored procedure? – Peter Penzov Feb 7 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 – Sabbir Hassan Feb 7 at 13:40
  • 1
    You still need use a transaction, even if you write a stored procedure to do this. – dbdemon Feb 7 at 13:57

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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