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.

54 Replies

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.

54 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. Jonathan Haddad

    I actually had to read through this twice, because I was working on something similar at the time. If you’re trying to do an update-join, and one of the tables has a trigger that updates the other table in the join, it will fail.

    For example, if you have these tables:
    picture (pictureid, name, num_comments)
    comment (commentid, pictureid, userid, comment, num_ratings),
    rating (commentid, userid, rating)

    Pictures have comments, comments have ratings.

    If you’re trying to copy a foreign key from the comments to the ratings (say the pictureid, for performance reasons), and you have a trigger on ratings to update an aggregate in comments (in this case, the rating count), this query will fail:

    update comment c JOIN rating r on c.commentid = r.commentid
    set r.pictureid = c.pictureid;

    You could get around it by only having the trigger fire if the rating changes.

  3. Phil

    Thanks Tracy. This drop/create trigger gambit appears to be standard methodology. BUT, I am trying out Navicat and this seems to stand in the way of normal MySQL execution/saving/defining of MySQL objects.

  4. cane

    Help with table, thanx up front!

    DROP TABLE IF EXISTS `donations`;
    CREATE TABLE `donations` (
    `id` int(11) NOT NULL auto_increment,
    `itemname` varchar(255) NOT NULL default ”,
    `gross_price` varchar(10) NOT NULL default ”,
    `pay_email` varchar(255) NOT NULL default ”,
    `paymentdate` date NOT NULL default ‘0000-00-00’,
    `firstname` varchar(100) default NULL,
    `lastname` varchar(100) default NULL,
    `memo` varchar(255) NOT NULL default ”,
    `paymentstatus` varchar(20) default NULL,
    `pendingreason` varchar(20) NOT NULL default ”,
    `txnid` varchar(30) NOT NULL default ”,
    `anom` char(3) NOT NULL default ‘no’,
    `stdate` date default NULL,
    KEY `paymentstatus` (`paymentstatus`),
    KEY `stdate` (`stdate`),
    KEY `txnid` (`txnid`)
    ) TYPE=MyISAM AUTO_INCREMENT=1;

    #1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key.

  7. Herman

    Hello!
    I need a catalog tree table. How can I create trigger to check a column with “level of subcatalog”?
    When some catalog moved into another all downlaying catalog must recount theirs level.
    This is possible?

  8. Michael Hasenstein

    Unfortunately this is NOT a solution to the problem. It only works if the record you want to update is the same record triggering the trigger.

    What if I want to update a DIFFERENT row in the same table? For example, I’ve a tree stored in the table and want to update the parent_id’s in all affected rows when a row (node in the tree) is deleted. Impossible! What’s more, there is no reason for this mysql behavior. I use INNODB, which should have row-level locks. I’d understand the error in MYISAM tables which use table-locking.

    Fact remains, it’s a really stupid mySQL bug. Oh well, one more reason to use a REAL database like Oracle 🙁

  10. Alvaro Oliver

    Michael, TMD: same problem here!
    what about updating different row in the same triggered table?

  12. Jhon Pierre

    I have a same problem. I create a trigger AFTER INSERT on tabla1 , in it i want to UPADTE the same tabla1 . But when i insert a row, i get the next error:

    Error Code : 1442
    Can’t update table ‘cuentacontable’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
    (32 ms taken)

    Please. Help me, Thanks.

  13. Jatin

    I got a msg when creating a view.

    Error Code : 1142
    CREATE VIEW command denied to user ‘root’@’user.com’ for table ‘tbl_ABC’

    thanks in Advance..

  14. toro

    I use it and work find

    DELIMITER |

    CREATE TRIGGER shop_insert
    BEFORE INSERT
    ON shops
    FOR EACH ROW
    BEGIN
    IF NEW.shop_id = NEW.shop_id THEN
    SET NEW.created = NOW();
    END IF;
    END

    DELIMITER ;

  15. cometinv3

    hola una pregunta nesecito ayuda nesecito hacer un trigger que cuando yo borre de una tabla un registro lo compare con una segunda tabla si el registro que fue borrado se en cuentra en la segunda tabla con el disparador se elimine cual seria mi solucion gracias espero sus respuestas en mysql podrian poner las posibles soluciones;

    yo cree este trigger pero no se si esta bien

    ——————————
    delimiter //
    create trigger borrar
    after delete on animales
    for each row begin
    delete animales,concursos from concursos,animales
    where animales.cve_animal = concursos.cve_animal;
    end
    // delimiter
    ————————————

    saludos

  24. Keith

    I had the same issue. I wanted to re-number the consecutive running line #’s of a master’s detail record when a detail line item is deleted. So update the same table. To do this I used a Stored Procedure for my delete, did the updates , then deleted the record. No other solution as of yet.

  28. MARLEY

    Hello, I´m having the same problem. But I using different databases. I need replicate data from a table on db1 to a same table on db2.

    please help-me.
    Thanks

  29. ofernandoseabra

    hey… i was trying to update a row like that trigger does and i got a success with this trigger…

    create trigger doJob before insert on table
    for each row begin
    if new.col1 is null then
    set new.col1 = date(now());
    end if;
    if new.col2 is null then
    set new.col2 = time(now());
    end if;
    end

    in the insert query, just inserted a value for PK/FK column and mysql done the date time job for me!

    cya!

  34. tas branded kw1

    Hey there, I think your website might be having browser compatibility issues.
    When I look at your website in Safari, it looks fine but
    when browsing in my web browser, it has some overlapping.
    I just wanted to give you a quick heads up!
    Other then that, terrific blog!

  36. roth ira rollover

    Thank you for the sensible critique. Me & my neighbor were just preparing to do some research on this. We got a grab a book from our local library but I think I learned more clear from this post. I’m very glad to see such wonderful information being shared freely out there.

  38. Bobe

    Oops, that wasn’t a good explanation.If you make an item rerueqid in form 1, this field ends up being rerueqid in the user’s profile when they edit their profile. If the user used another registration form (form 2), they’ll have no clue as to where this new field came from and you don’t want them to fill in a value for this field anyways. Is there any way to get around this? Thanks!

  41. witney sahagun

    my friend wanted IRS 7004 several days ago and located a great service that has an online forms library . If others want IRS 7004 too , here’s a https://goo.gl/k11jrT

  42. auteurdelivre.com

    Terrific w?rk! That ?s th? kind of informati?n t?at are supposed to be shared acr?ss th? internet.
    Disdgrace ?n thhe seek engines foor not positioning t??? publish upper!
    Comme on o??r and discuss wit? mmy site .
    Th?nk y?u =)

  49. pandora bracelet

    I wish to show some appreciation to you for rescuing me from such a issue. After surfing throughout the internet and getting tips that were not powerful, I was thinking my entire life was well over. Living minus the answers to the difficulties you have fixed by means of your entire site is a critical case, as well as those that would have negatively affected my career if I hadn’t noticed your website. Your own personal talents and kindness in playing with every part was priceless. I’m not sure what I would have done if I had not discovered such a stuff like this. I am able to at this moment look forward to my future. Thanks for your time very much for your specialized and result oriented guide. I will not hesitate to recommend your web sites to anybody who needs to have tips about this issue.

  50. off white jordan 1

    A lot of thanks for all of the efforts on this site. My niece delights in getting into internet research and it is simple to grasp why. A lot of people notice all about the powerful ways you convey rewarding secrets through the web site and as well as recommend contribution from others on this article and my simple princess is truly understanding a lot of things. Enjoy the remaining portion of the year. You’re the one performing a stunning job.

Leave a Reply

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