从table1和表2中关联的记录中删除

Question

I'm trying to make a simple decision deleting records from database. I have a main table with records and the unique ID it's auto increment.

Then I have a second table that stores values(could be more than one) associating the ID from the first table.

Example:

table1
editors
ID (autoincrement)
EditorName

Then I have a second table with:

table2
ID
editorID (same ID as table1)
bookname

So this way I want to know how could be the best way to delete a record from table1 (example ID=2) and it automatically deletes all records from table2 where editorID=2.

I've tried many ways but it seems they don't work unless I do two queries but I don't like that way. There's a smarter way to do that? It must be by code or I could associate two tables in SQL and they associated by ID and editorID?

Answer 1

Solution #1:

If you have foreign key constraint in table2 which refers to the ID of table1 having ON DELETE CASCADE behavior then deleting records from table1 will delete the corresponding entries from table2.

Solution #2:

You can delete multiple tables using JOIN

DELETE T1,T2
FROM table1 AS T1
INNER JOIN table2 AS T2 ON T1.ID = T2.editorID
WHERE T1.ID = 2;

See <kbd>Related post</kbd>

---

EDIT:

In order to delete entries from table1 and table2 irrespective of whether corresponding entries exist in table2 or not you need to replace the INNER JOIN by LEFT JOIN

  DELETE T1,T2
  FROM table1 AS T1
  LEFT JOIN table2 AS T2 ON T1.ID = T2.editorID
  WHERE T1.ID = 2;