ERROR 1442: Can’t update table ‘?′ in stored function/trigger because it is already used by statement which invoked this
ERROR 1442: Can’t update table ‘?′ in stored function/trigger because it is already used by statement which invoked this
Questions tagged [mysql-error-1442]
40 questions
19
votes
2 answers
Updating table in trigger after update on the same table
How can I update table's column in a trigger after update on the same table?
Here's the trigger:
CREATE TRIGGER upd_total_votes AFTER UPDATE ON products_score
FOR EACH ROW
UPDATE
products_score
SET
…
nhaa123
- 9,570
- 11
- 42
- 63
13
votes
2 answers
How to Update same table on deletion in MYSQL?
In my database I have a table Employee that has recursive association (an employee can be boss of other employee):
create table if not exists `employee` (
`SSN` varchar(64) not null,
`name` varchar(64) default null,
`designation` varchar(128)…
Grijesh Chauhan
- 57,103
- 20
- 141
- 208
12
votes
1 answer
MySQL: How to create trigger for setting creation date for new rows
I ran into a problem as I tried to create two TIMESTAMP columns in my database. One called created and one called updated. I figured it would be easy to set the default value of both to CURRENT_TIMESTAMP and then ON UPDATE CURRENT_TIMESTAMP for the…
Svish
- 152,914
- 173
- 462
- 620
11
votes
2 answers
mysql trigger stored trigger is already used by statement which invoked stored trigger
I want to set up a trigger so that if on an update the prediction field is = 3 then the trigger changes the value to 4 and saves it in the database. The trigger is below.
For some reason I keep getting an error saying:
#1442 - Can't update table…
Russ
- 115
- 1
- 2
- 9
6
votes
1 answer
what is the real cause of mysql error 1442?
well i have looked for a lot of places on the internet for the cause of the mysql error #1442 which says
Can't update table 'unlucky_table' in stored function/trigger because
it is already used by statement which invoked this stored
…
lovesh
- 5,235
- 9
- 62
- 93
6
votes
3 answers
Find node level in a tree
I have a tree (nested categories) stored as follows:
CREATE TABLE `category` (
`category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category_name` varchar(100) NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY…
Álvaro González
- 142,137
- 41
- 261
- 360
5
votes
1 answer
Update same row in a trigger in mysql
I have a table SCHEDULES_CLASS, 2 of it's fields are CLASS_ID(foreign key from the CLASS table), STATUS and NUMBER_OF_ENROLLED_STUDENTS. I have other table CLASS having 1 field as MAX_SEATS.
When the NUMBER_OF_ENROLLED_STUDENTS in a scheduled class…
mtk
- 13,221
- 16
- 72
- 112
4
votes
1 answer
MySQL after update, update datetime column of same table
I have a table called teamMembers, this table stores a name and an e-mail address of a person:
id INT (UNIQUE, AUTOINCREMENT)
name VARCHAR(255)
email VARCHAR(255)
updated DATETIME
created TIMESTAMP
I've created my trigger using…
user936965
3
votes
2 answers
MySql Triggers to delete child records in the same table
I have a table that stores parent and child records in it.
I was trying to create a trigger that would delete all child records when the parent is deleted:
Delete From tbl Where ParentId = OLD.Id
While I can save the trigger successfully, when…
IgalSt
- 1,974
- 2
- 17
- 26
3
votes
1 answer
#1442 - Can't update table '*' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
I'm really bad at SQL, but I'm trying to keep my database simple and minimalistic as possible. Not duplicating values but using references and id's instead.
Now after inserting a query I get error
1442 - Can't update table 'signed' in stored…
Vergil333
- 505
- 1
- 7
- 14
3
votes
1 answer
MySQL trigger - shifting internal order column
I have the following table [pages]:
pgid|pgname|pgorder
----+------+-------
1 |Page#1| 1
2 |Page#2| 2
3 |Page#3| 3
4 |Page#4| 4
Column 'pgorder' represents position of particular page.
I need trigger which would after delete of one…
sbrbot
- 6,169
- 6
- 43
- 74
2
votes
2 answers
ERROR 1442: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
I have a table that I want : when the table has been updated, 2 fields of that (title and description) change and take value from another table
This is my trigger:
drop trigger trigger_trade_request ;
CREATE TRIGGER trigger_trade_request AFTER…
afsane
- 1,889
- 6
- 25
- 40
2
votes
2 answers
MYSQL UPDATE Table on INSERT into same table
it seems simple, but still a challenge. I simplified my issue as much as possible.
I have this test_table with one record:
id | cost_per_record
1 | 24
After an INSERT I want the table to look like this:
id | cost_per_record
1 |…
Frits Nagtegaal
- 61
- 1
- 6
1
vote
0 answers
Error Code: 1442. Can't update table 'ArgentineWine' in stored function/trigger because it is already used by statement
I have read the 4 cases in this forum reg.
Error Code: 1442. Can't update table 'tablename' in stored function/trigger because it is already used by statement
Those cases are way more complex than my situation. I created an ArgentineWines table…
David Hernandez
- 11
- 1
1
vote
1 answer
MySQL Deleting Row in an After Insert Trigger Error
I have three tables in a MySQL database, bids, offers and matched. The matched table contains bids and offers which have been matched. There is an event that runs the logic to do the matching shown below which works.
CREATE EVENT…
WK123
- 620
- 7
- 18