Is there any other way to delete rows from multiple tables without creating foreign key between tables?

Question

This code does not work. Without creating foreign key between tables how can I delete data from multiple tables that matches the conditions? How can I write a 2 query and send it with PHP-PDO

$query = "DELETE FROM category, bookmark WHERE (bookmark.category = ? AND category.name = ?)";
$stmt = $db->prepare($query);
$stmt->execute([$categoryName, $categoryName]);

I am using "?" to prevent from sql injection. This is the error I get.

JavaSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (bookmark.category = 'Java' AND category.name = 'Java')' at line 1

[How to delete from multiple tables in MySQL?](https://stackoverflow.com/q/3331992) — Solarflare, May 18 '20 at 20:06

score 1 · Answer 1 · answered May 18 '20 at 20:17

In multi-table DELETE you MUST specify the tables which records must be deleted:

DELETE category, bookmark
FROM category, bookmark 
WHERE (bookmark.category = ? AND category.name = ?)

See MySQL 8.0 Reference Manual / ... / DELETE Statement, section "Multiple-Table Syntax". The names of tables which records must be deleted are NOT enclosed into square brackets as optonal (2nd row of query text in both syntax variants).

Is there any other way to delete rows from multiple tables without creating foreign key between tables?

1 Answers1