MySQL: Solution for ERROR 1442 (HY000): Can’t update table ‘t1’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Here is a sample table you can create to test following problem/solution on:

CREATE TABLE `t1` (
`a` char(1) default NULL,
`b` smallint(6) default NULL
);
insert into t1 values ('y','1');

I have a table t1 which has column a and b, I want column a to be updated to ‘n’ when column b = 0. Here is the first version I created:

DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1
FOR EACH ROW UPDATE t1 SET a= 'n' WHERE b=0;
|
DELIMITER ;

The trigger created successfully but I got this error when I tried to do an update on column b on table t1:
mysql> update t1 set b=0;
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

After searching online for a while and trying different solutions, I finally found a way to update the table which has trigger on it:

drop trigger trigger1;
DELIMITER |
CREATE TRIGGER trigger1 BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
IF NEW.b=0 THEN
SET NEW.a = 'n';
END IF;
END
|
DELIMITER ;

After the new trigger is in, I issued the same update query and “ERROR 1442 (HY000): Can’t update table ‘t1’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.” didn’t show up and it updated the col a value to “n” as it suppose to.

mysql> update t1 set b=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1\G
*************************** 1. row ***************************
a: n
b: 0

Therefore, if you want to create a trigger on the table which will update itself, make sure you use the NEW.column_name to refer to the row after it’s updated and don’t do the full update statement!

However, if you are updating some other table, then you can use the regular update statement:

DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1
FOR EACH ROW UPDATE t2 SET a= 'n' WHERE b=0;
|
DELIMITER ;

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

52 thoughts on “MySQL: Solution for ERROR 1442 (HY000): Can’t update table ‘t1’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

  1. fenty puma

    I needed to send you that tiny note to help thank you as before considering the spectacular knowledge you have featured here. This has been really extremely generous with people like you to offer extensively all a few individuals would have marketed as an e book to help with making some bucks for their own end, notably considering that you might have done it if you considered necessary. The guidelines also acted to become great way to fully grasp that most people have the identical interest just like my own to figure out more when considering this matter. I’m certain there are millions of more pleasant moments ahead for folks who read carefully your blog post.

  2. ferragamo sale

    Thanks for all of the hard work on this web site. Gloria really likes participating in research and it’s really easy to see why. A lot of people hear all regarding the powerful mode you present powerful thoughts by means of the web site and in addition increase participation from other individuals about this theme plus our daughter is learning a lot of things. Take pleasure in the remaining portion of the new year. You have been conducting a wonderful job.

Leave a Reply

Your email address will not be published. Required fields are marked *