// MySQL error reference

MySQL error 1442: Trigger can't update its own table

A trigger or stored function tries to modify the same table as the statement that fired it, which MySQL doesn't allow.

Error number
1442
Name
ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
SQLSTATE
HY000
Category
Databases, tables and columns
Error message
Can't update table '%s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

What it means

In MySQL a trigger can't run INSERT, UPDATE or DELETE on the table the triggering statement is changing, and a stored function can't modify a table the calling statement uses. The way to change the row being written is to set NEW.column in a BEFORE trigger.

Common causes

  • An AFTER UPDATE trigger that runs UPDATE on its own table, for example to set a timestamp.
  • A function called from a statement that modifies the same table.

How to fix it

  1. Change the row itself in a BEFORE trigger.

    CREATE TRIGGER orders_touch BEFORE UPDATE ON orders
    FOR EACH ROW SET NEW.updated_at = NOW();
  2. Or let the column do it: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

  3. Move changes to other rows of the same table into application code or a stored procedure called after the statement.

Related errors

More databases, tables and columns errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.