error 1093, but there is no selection in the subquery

Asked Nov 10 '19 at 02:55

Active Nov 10 '19 at 02:55

Viewed 24 times

I´m creating a simple procedure to delete a row from the table tbGame, but when I try to call the proc it returns the error 1093

I have tried to figure out what's happening by deleting the row using the normal command(DELETE FROM <table>) but it didn´t work. After researching a bit I found that this error usually happens when there is a SELECT in the subquery, but the procedure has none, I also tried to turn off the derived_merge but it didn´t work too,in the end I created another database and tried the same procedure and it worked, so I think the problem is not in my procedure.


---turning off derived_merge

SET @@optimizer_switch='derived_merge=OFF';

-- that´s the procedure
DELIMITER //

CREATE PROCEDURE sp_DeleteGame (in _idGame int)

begin
    delete from tbGame where idGame = _idGame;
end // 

DELIMITER ;


-- that´s the normal delete command that I used

DELETE FROM tbGame WHERE idGame = 1;

In both cases Mysql returns:

'Error Code: 1093. You can't specify target table 'tbGame' for update in FROM clause'

asked Nov 10 '19 at 02:55

pipoca_com_sazom

1

The procedure you showed us should not generate this error message AFAIK, but this assumes that you are actually running the above code. Is there any chance that you are still somehow running the older version? – Tim Biegeleisen Nov 10 '19 at 02:58
2

Any triggers on the table? – sticky bit Nov 10 '19 at 03:04
Is `tbGame` a `VIEW`? – Nick Nov 10 '19 at 03:45
@sticky bit yes,there is a trigger on the table – pipoca_com_sazom Nov 10 '19 at 12:31
@Nick no it’s a common table – pipoca_com_sazom Nov 10 '19 at 12:32
@Tim Biegeleisen I checked this a few times and it's running the correct version – pipoca_com_sazom Nov 10 '19 at 12:37
The problem is almost certainly occurring in the trigger, can you [edit] your question and add the code for that? – Nick Nov 10 '19 at 21:51

error 1093, but there is no selection in the subquery

0 Answers0