Delete from different tables in one query with prepared statements

Question

I want to delete multiple records with a specified value (non-unique) in one table and delete the record with that same specified value (unique) in another table using a prepared statement. This is what I got,

$stmt = $conn->prepare("DELETE FROM Books WHERE Genre_ID=? AND FROM Library WHERE Genre_ID=?");
$stmt->bind_param("ii", $genreID, $genreID);

The aim of the query would be to delete the specified Genre from the Library table by its ID and, of course, remove all the books from the Books table with that ID.

Can a book only have one genre? I'd suggest looking into foreign keys on setting up "on delete cascade" or similar. https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete — JimL, May 16 '18 at 21:27

spencer7593 · Answer 1 · 2018-05-16T21:43:31.467

This would better be handled with two statements... one to delete from Books, and another to delete from Library.

To get this done in a single statement, we could use a join operation. But there is an issue with "missing" rows.

My suggestion:

Write a SELECT statement first, get that tested, and then convert it into a DELETE statement.

 SELECT b.*
      , l.*
   FROM `Books` b
   JOIN `Library` l 
     ON l.genre_id = b.genre_id 
  WHERE b.genre_id = ?

But this statement won't return any rows if there isn't at least one row in both Books and Library with a matching genre_id.

We can partly fix that with an outer join.

 SELECT b.*
      , l.*
   FROM `Library` l 
   LEFT
   JOIN `Books` b
     ON b.genre_id = l.genre_id 
  WHERE l.genre_id = ?

If there's at least one row in Library with the specified genre_id, we will get all of the Books with that same genre_id. But it won't return any rows if there isn't a matching row in Library.

And MySQL doesn't support full outer joins, so that's as far as we get. We can make either Library or Books the driving table... we can handle "missing rows" from one table or the other.

EDIT

As an ugly workaround

SELECT b.*
     , l.*
  FROM ( SELECT rb.genre_id
           FROM Books rb
          WHERE rb.genre_id = ?
          GROUP BY rb.genre_id
          UNION
         SELECT rl.genre_id
           FROM Library rl
          WHERE rl.genre_id = ?
          GROUP BY rl.genre_id
       ) r
  LEFT
  JOIN Books b
    ON b.genre_id = r.genre_id
  LEFT
  JOIN Library l
    ON l.genre_id = r.genre_id

Then we can convert that into a DELETE statement, replacing SELECT with DELETE

with the missing rows problem:

 DELETE b.*
      , l.*
   FROM `Library` l
   LEFT
   JOIN `Books` b
     ON b.genre_id = l.genre_id 
  WHERE l.genre_id = ?

Note the same issue... this won't remove any rows from Books if there isn't at least one matching row in Library.

EDIT

or with the ugly workaround. convert the SELECT into a DELETE:

DELETE b.*
     , l.*
  FROM ( SELECT rb.genre_id
           FROM Books rb
          WHERE rb.genre_id = ?
          GROUP BY rb.genre_id
          UNION
         SELECT rl.genre_id
           FROM Library rl
          WHERE rl.genre_id = ?
          GROUP BY rl.genre_id
       ) r
  LEFT
  JOIN Books b
    ON b.genre_id = r.genre_id
  LEFT
  JOIN Library l
    ON l.genre_id = r.genre_id

Again, I recommend that this operation be performed as two separate statements, one to remove rows from Books and another to remove rows from Library.

I suspect there isn't a foreign key constraint between these two tables. If there was, the order that these deletes are performed would be important.

Delete from different tables in one query with prepared statements

1 Answers1