MySQL Delete

DELETE removes rows. Missing WHERE = empty table. Always dry-run with a SELECT first.

Single row

$stmt = $pdo->prepare('DELETE FROM customers WHERE id = ?');
$stmt->execute([42]);

echo $stmt->rowCount(), ' rows affected';

Multiple rows by condition

$stmt = $pdo->prepare(
    'DELETE FROM logins WHERE created_at < ?'
);
$stmt->execute([(new DateTimeImmutable('-90 days'))->format('Y-m-d')]);

Safety patterns

1. SELECT first. Same WHERE clause — make sure the row count looks right:
   PHP

   $stmt = $pdo->prepare('SELECT COUNT(*) FROM customers WHERE country = ?');
   $stmt->execute(['XX']);
   echo $stmt->fetchColumn(), ' rows would be deleted';

2. Wrap in a transaction. If something looks wrong, roll back:
   PHP

   $pdo->beginTransaction();
   $stmt = $pdo->prepare('DELETE FROM customers WHERE country = ?');
   $stmt->execute(['XX']);
   
   if ($stmt->rowCount() > 100) {
       $pdo->rollBack();    // too many — abort
       die('Aborted; check the WHERE');
   }
   $pdo->commit();

3. Soft delete. Many production schemas don't delete; they flag:
   SQL

   UPDATE customers SET deleted_at = NOW() WHERE id = 42;

   Queries then filter WHERE deleted_at IS NULL.

Cascade behaviour

If customers has a foreign key from orders, a DELETE FROM customers may:

• RESTRICT — fail (default in InnoDB without ON DELETE).
• CASCADE — also delete the matching orders.
• SET NULL — null out the customer_id on orders.

Pick at table-create time with ON DELETE.

Example

<?php
$stmt = $pdo->prepare('DELETE FROM customers WHERE id = ?');
$stmt->execute([42]);
echo $stmt->rowCount();

Exercise

Test yourself