EC
1442
MySQLERRORNotableStored RoutinesHIGH confidence

Cannot update table already in use by the invoking statement

Production Risk

Medium — the triggering statement will fail with this error.

What this means

ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG (1442, SQLSTATE HY000) is raised when a stored function or trigger attempts to modify a table that is already being read or written by the statement that invoked it.

Why it happens
  1. 1A trigger on table T tries to UPDATE or INSERT into table T
  2. 2A stored function called within a query on table T tries to modify table T
How to reproduce
trigger — this will error
trigger — this will error
CREATE TRIGGER trg AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE orders SET total = NEW.amount WHERE id = NEW.id; -- Same table!
END;

expected output

ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Fix

Use BEFORE trigger to modify NEW values directly

Use BEFORE trigger to modify NEW values directly
CREATE TRIGGER trg BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.total = NEW.amount * 1.1;  -- Modify before insert, no extra UPDATE needed
END;

Why this works

BEFORE triggers can modify NEW.column values directly, avoiding the need to UPDATE the same table.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1442 ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors