Search code examples
mysqlsqlsql-delete

How do I delete data from multiple tables?

I have facing problem to delete all data from multiple tables? Anyone can guide me how to delete data from multiple tables? I am using MySQL.

Scenario:

I want delete data in four table according the first table "transaction_main" column name "id"

  • first table name is transaction_main and column name is id;
  • Second table name is transaction_point and column name is id_transaction_main;
  • third table name is total_earning and column name is id_transaction;
  • fourth table name is transaction_credit and column name is id_transaction_main;

May I know using below method to delete data?

  DELETE t1,t2 

  FROM table1 AS t1 

  INNER JOIN table2 t2 ...

  INNER JOIN table3 t3 ...
Solution

  • if the tables has a relation you should configure the on delete cascade in the foreign key constraints...

    the foreign key creation would be like

    ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (fk_id) REFERENCES dbo.T1(pk_id) ON DELETE CASCADE