0

I have created a table test1 as

CREATE TABLE test1 (id INT, name VARCHAR(20)) ENGINE=INNODB;

Now I want to delete a record from this table before inserting a new record having the same id(that is,record already existing with same id,then insert the new record) .I tried the code give below.

create trigger before_insert_test1 before insert
on test1
for each row
begin
delete from test1 where id=NEW.id;
end;

but it shows error ERROR 1442 (HY000): Can't update table 'test1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Can anyone help. thanks in advance

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
spc
  • 131
  • 5
  • 19
  • Your question is similar to this one: http://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already – valicu2000 Jan 18 '15 at 10:59

2 Answers2

0

I don't think you can update/delete from the same table that you're inserting into. The table is being locked by the insert statement, even though you specified after insert. The insert transaction is not yet completed.

My suggestion would be to do a test and delete the record before you do the insert, or use the MySQL ON DUPLICATE KEY UPDATE statement if it applies

Spock
  • 4,700
  • 2
  • 16
  • 21
-1
create trigger before_insert_test1 before insert on test1
DELETE FROM test1 WHERE id=NEW.id;
end;

You shan't need for loop, delete statement will delete every record with given id.

adeam
  • 73
  • 7
  • 1
    I tried your solution and got the same error as @spc i.e. ```Failed!Can't update table 'table_one' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.``` Do you have any other suggestions? – Hmerman6006 Sep 30 '19 at 20:46