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
  • 125,519
  • 44
  • 317
  • 399
RiCHiE
  • 238
  • 2
  • 13
  • 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
  • 81,091
  • 16
  • 198
  • 255
  • 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