sql Inner join delete

Question

I have tow tables

tests_sub ,tests_sub_sub

and tests_sub.id = tests_sub_sub.id

so i want to delete data from both tables with one

sql query

I used the following inner join

DELETE tests_sub, tests_sub_sub FROM tests_sub
INNER JOIN tests_sub_sub ON tests_sub_sub.id = tests_sub.id
WHERE tests_sub.id = 10

the query works ok only if both tables have entries for the tests_sub.id... ( if tests_sub_sub has no entry for test_sub.id = 10 ... although the table tests_sub has entries for that id no rows are affected ... please suggest some tips....

Please refer to this link to delete using join http://stackoverflow.com/questions/652770/delete-with-join-in-mysql — Deepak Rai, Oct 15 '13 at 07:12
Inner join produces only the set of records that match in both Table A and Table B. Try using `Left Outer Join` — heretolearn, Oct 15 '13 at 07:15

score 1 · Accepted Answer · answered Oct 15 '13 at 07:12

1

use LEFT JOIN.

DELETE tests_sub, tests_sub_sub 
FROM   tests_sub
       LEFT JOIN tests_sub_sub 
          ON tests_sub_sub.id = tests_sub.id
WHERE  tests_sub.id = 10

answered Oct 15 '13 at 07:12

John Woo

258,903
69
498
492

thanks bro....but how to if there are 3 tables ???......................DELETE tests,tests_sub FROM tests LEFT JOIN tests_sub ON tests_sub.idtests = tests.idtests LEFT JOIN tests_sub_sub ON tests_sub_sub.idtests_sub = tests_sub.idtests_sub WHERE tests.idtests = 10 – sx3 Oct 15 '13 at 07:35
the same, add `DELETE a, b, c FROM a LEFT JOIN b ON .... LEFT JOIN c on .... WHERE ....` -- if you want to delete in 3 tables. – John Woo Oct 15 '13 at 07:36

sql Inner join delete

1 Answers1