1

I want to delete tableA while removing all rows in tableB that have matching tableA.A_ID = tableB.A_ID

Currently I have a foreign key set to CASCADE on delete and update set on TableB for tableB.A_ID.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
RiCHiE
  • 278
  • 3
  • 14
  • depending on which direction you are going. Take a look at http://stackoverflow.com/a/2917374/1816093 – Drew Dec 04 '15 at 09:11

1 Answers1

1

Turn constraints temporarily off by

SET SQL_SAFE_UPDATES = 0;

DELETE FROM tableB b WHERE EXISTS ( SELECT * FROM tableA a WHERE a.A_ID = b.A_ID )

DELETE FROM tableA;

SET SQL_SAFE_UPDATES = 1;
davidkonrad
  • 83,997
  • 17
  • 205
  • 265
  • So turn off checks then delete the rows in tableB then delete tableA? – RiCHiE Dec 04 '15 at 09:22
  • @RiCHiE, I thought it was about the constraints only. See update, **please backup your tables before you try it out**! – davidkonrad Dec 04 '15 at 09:31
  • Hmm I'm still getting an error in MySQL workbench "You are using safe mode" although i disabled safe mode in options as well. Do I need to execute in shell? – RiCHiE Dec 04 '15 at 09:39
  • @RiCHiE, see update, honestly cant say if it works - found it here -> http://stackoverflow.com/questions/21841353/mysql-delete-under-safe-mode ...must admit I have never experienced that error message myself. – davidkonrad Dec 04 '15 at 09:43