How to Correctly Delete Rows Across Multiple Tables in MySQL?

MySQL cross-table deletion: syntax error troubleshooting

In MySQL, deleting rows from multiple tables simultaneously requires special query syntax. When trying to execute the following delete query:

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

You may encounter the following error:

<code>Uncaught Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual...</code>

This error stems from a syntax error in cross-table deletion. To solve this problem, use JOIN in DELETE 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, to delete only from the pets_activities table, you can use:

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

By using the JOIN statement in this way, you can efficiently perform cross-table delete operations in MySQL. Keep in mind that this approach also works for single-table deletes with referential integrity.

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