Delete from table1 and records associated in table 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?

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

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 Related post

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;

edited May 23 '17 at 12:06

Community

1
1

answered Sep 22 '16 at 04:17

1000111

13,169
2
28
37

It works if table1 and 2 have values. Imagine that only table1 have values. Since I want to delete both, it doesn't delete as table2 doesn't have vlaues. Any way to do that? – Tiago Sep 22 '16 at 19:15
just replace the `inner join` by `left join`. Please check the updated answer under **edit** section. @Tiago – 1000111 Sep 24 '16 at 07:19

score 0 · Answer 2 · edited Sep 21 '16 at 18:24

0

make a foreign key for linking those 2 table

Define a foreign key between these two tables, and let MySQL know when to cascade whenever an editor is deleted:

CREATE TABLE editors (
  ID int(11) NOT NULL AUTO_INCREMENT,
  EditorName varchar(255) NOT NULL,
  PRIMARY KEY (ID)
)engine=InnoDB;

CREATE TABLE edits (
  ID int(11) NOT NULL AUTO_INCREMENT,
  bookname varchar(255) NOT NULL,
  editorID int(11) NOT NULL,
  PRIMARY KEY (ID),
  KEY editorID (editorID),
  CONSTRAINT edits_ibfk_1 
  FOREIGN KEY (editorID) 
  REFERENCES editors (ID) 
  ON DELETE CASCADE
)engine=InnoDB;

edited Sep 21 '16 at 18:24

Drew

24,851
10
43
78

answered Sep 21 '16 at 17:58

mpp1996

11
1

Nowhere near enough information to be considered an answer – RiggsFolly Sep 21 '16 at 18:04
I agree it's not enough information, but he had the correct idea. I added a code sample to demonstrate this. Using triggers work too, but it's difficult to setup in comparison to just setting a foreign key. – Dave Chen Sep 21 '16 at 18:09
@DaveChen how is setting up a trigger difficult and where in the question does it reference a parent child relationship that would generate this answer. Oh, and there is a missing comma – Drew Sep 21 '16 at 18:18
Oops my bad. And the question mentions editors and edits to books. Table 1 contains all the editors, and table 2 contains all the edits. All edits belong to an editor, thus a parent/child relationship is it not? – Dave Chen Sep 21 '16 at 18:21
That I can buy into @DaveChen my bad – Drew Sep 21 '16 at 18:24

score -1 · Answer 3 · answered Sep 21 '16 at 18:40

-1

Check this.

DELETE table1
FROM table1
INNER JOIN table2 ON table1.ID = table2.editorID
WHERE table1.ID = 2;

answered Sep 21 '16 at 18:40

perrete

1

Delete from table1 and records associated in table 2

3 Answers3