MySQL query fails while performing conditional delete

Question

I have the following MySQL table:

CREATE TABLE IF NOT EXISTS otu_tokens (
  otu_token_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  otu_token_ref_id VARCHAR(36) NOT NULL,
  otu_is_claimed INTEGER,
  otu_expires_on DATETIME,

  CONSTRAINT pk_otu_tokens PRIMARY KEY (otu_token_id),

  INDEX idx_otu_tokens_ref_id (otu_token_ref_id),

  CONSTRAINT uc_otu_tokens_ref_id UNIQUE (otu_token_ref_id)
);

I want to run a query that deletes records if either:

1. they have been "claimed" (otu_is_claimed = 1); OR
2. they have expired (otu_expires_on < NOW IN UTC)

The UTC part is important; the records will be stored with their otu_expires_on values being set to UTC, so I need to make sure that the "NOW IN UTC" comparison actually uses UTC.

My best attempt:

DELETE FROM otu_tokens
WHERE otu_token_id IN (
  SELECT otu.otu_token_id
  FROM otu_tokens otu
  WHERE
    otu.otu_is_claimed = 1
    OR
    otu.otu_expires_on < UTC_TIMESTAMP()
);

Produces the following error:

ERROR 1093 (HY000): You can't specify target table 'otu_tokens' for update in FROM clause

What can I do to fix this query so that it behaves the way I need?

Answer 1

I don't see the point for the subquery, that returns values of the primary key column to the outer query. This would seem to do what you ask for:

DELETE FROM otu_tokens
WHERE otu_is_claimed = 1 OR otu_expires_on < UTC_TIMESTAMP()