online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

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.


33 Responses to “ 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. March 3rd, 2008 | 6:53 pm

    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.

  2. Steve
    March 5th, 2008 | 11:39 pm

    Thanks for the great post. It helped me solve my problem with setting up my first trigger.

  3. Phil
    June 10th, 2008 | 1:39 am

    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
    June 14th, 2008 | 8:19 pm

    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.

  5. June 15th, 2008 | 4:29 pm

    Cane, please use forums for questions not related to posts. I posted a correct table definition at: http://crazytoon.com/forum/viewtopic.php?f=3&t=6

  6. October 2nd, 2008 | 12:34 am

    Hi friend,
    I am facing problem in creating database. Would you please help me.

  7. November 17th, 2008 | 3:07 am

    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. January 17th, 2009 | 7:23 pm

    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 :-(

  9. TMD
    March 16th, 2009 | 2:45 am

    i need a solution to update DIFFERENT row in same table too, who can help??

  10. Alvaro Oliver
    April 29th, 2009 | 12:40 pm

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

  11. June 1st, 2009 | 8:31 am

    php myadmin error

  12. Jhon Pierre
    June 10th, 2009 | 7:38 am

    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
    June 24th, 2009 | 4:23 am

    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
    October 18th, 2009 | 9:03 pm

    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
    December 14th, 2009 | 8:57 pm

    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

  16. June 9th, 2010 | 6:33 am

    Hi Tracy, thank you so much for sharing this, you really save my time in searching for answer.

  17. June 12th, 2010 | 12:57 am

    Yeah,, i have the same problem too,,
    thanks for sharing,,

  18. bruno
    March 4th, 2011 | 10:38 pm

    ffsfsfs

  19. bruno
    March 4th, 2011 | 10:39 pm

    ijjijiji

  20. bruno
    March 4th, 2011 | 10:40 pm

    lol tenso

  21. bruno
    March 4th, 2011 | 10:40 pm

    lol tenso 0,2

  22. March 4th, 2011 | 10:40 pm

    tenso 03

  23. March 7th, 2011 | 8:22 am

    So no one solution for different rows :(

  24. August 2nd, 2011 | 8:40 pm

    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.

  25. Boom
    October 20th, 2011 | 5:19 am

    So great solution. Works great.

  26. Quang
    December 20th, 2011 | 3:12 pm

    Works great!

    Thanks a lot

  27. carlos
    December 23rd, 2011 | 11:21 pm

    puto

  28. MARLEY
    April 16th, 2012 | 3:54 pm

    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. August 10th, 2012 | 4:30 am

    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!

  30. May 14th, 2013 | 6:00 am

    For knowledge :
    Under Oracle, what you want to do could be achieved with a *statement* trigger, not a *for each row* one.
    Unfortunately, only “for each row” triggers exist under mysql. ;)

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm

  31. ika
    June 12th, 2013 | 7:30 pm

    Thanks for the solution, it helped me solve my problem

  32. December 17th, 2013 | 1:11 am

    why cant i open localhost/phpmyadmin

  33. March 12th, 2014 | 5:08 pm

    I aall the time used to read piece of wfiting in nees papers but now
    as I am a user of web therefore from now I am using net for
    posts, thanks to web.

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image