How to Safely Delete Records from Multiple MySQL Tables?

MySQL multi-table safe deletion of records

Directly using the following code to delete rows from multiple tables simultaneously may result in an error:

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

The recommended solution is to use the JOIN statement:

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

Alternatively, you can just delete the records in the pets_activities table while referencing the pets table:

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

This approach uses an alias in the FROM clause to specify the table to be dropped, and is particularly useful when dealing with complex scenarios with referential integrity.

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