How to Properly Delete Records from Multiple Tables in MySQL?

MySQL multi-table record deletion skills

Deleting records from multiple MySQL tables simultaneously is a complex task. Directly using a DELETE statement with a WHERE clause that contains multiple table references will usually fail. For example, the following code:

<code class="language-sql">DELETE FROM `pets` p,
            `pets_activities` pa
      WHERE p.`order` > :order
        AND p.`pet_id` = :pet_id
        AND pa.`id` = p.`pet_id`</code>

Usually produces errors like this:

Uncaught Database_Exception [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 'p, pets_activities pa...'

The correct approach is to use the JOIN keyword. The modified code is as follows:

<code class="language-sql">DELETE p, pa
      FROM pets p
      JOIN pets_activities pa ON pa.id = p.pet_id
     WHERE p.order > :order
       AND p.pet_id = :pet_id</code>

If you only need to delete records in the pets_activities table, you can use the following code:

<code class="language-sql">DELETE pa
      FROM pets_activities pa
      JOIN pets p ON pa.id = p.pet_id
 WHERE p.order > :order
   AND p.pet_id = :pet_id</code>

These methods can effectively delete records from multiple tables in MySQL. Of course, there are other methods for single-table deletion involving referential integrity, such as EXISTS, NOT EXISTS, IN, and NOT IN. However, the above MySQL syntax provides a general solution for multi-table deletion, especially when dealing with complex deletion scenarios.

The above is the detailed content of How to Properly Delete Records from Multiple Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!