Delete Rows Based On A Condition In Another Table

Remove rows from one table when a matching row exists in another table using a subquery.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Remove sessions belonging to banned users

User 2 appears in banned_users, so both of their sessions (ids 2 and 4) are deleted. Users 1 and 3 have no match in banned_users and their sessions remain. The correlated EXISTS subquery re-evaluates for each row in sessions, stopping as soon as a match is found.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE sessions (id INT, user_id INT);

CREATE TABLE banned_users (user_id INT);

INSERT INTO
  sessions (id, user_id)
VALUES
  (1, 1),
  (2, 2),
  (3, 3),
  (4, 2);

INSERT INTO
  banned_users (user_id)
VALUES
  (2);

Validated query Shared across supported engines.

SQL

DELETE FROM sessions
WHERE
  EXISTS (
    SELECT
      1
    FROM
      banned_users
    WHERE
      banned_users.user_id = sessions.user_id
  );

SELECT
  id,
  user_id
FROM
  sessions
ORDER BY
  id;

Expected result Returned rows for the shared example.

id	user_id
1	1
3	3

Output is identical across all engines.

Useful when

Where this command helps.

removing session or activity rows when the associated user has been banned
cleaning up child records when the parent matches a condition in a separate table

Explanation

What the command is doing.

Deleting rows conditionally based on related data uses a subquery in the WHERE clause. The IN form checks whether a column value appears in a set returned by the subquery. The EXISTS form uses a correlated subquery and is often more efficient when the inner table is large, because the database stops evaluating after the first match.

Delete Rows Based On A Condition In Another Table

Remove sessions belonging to banned users

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.