mysql trigger delete on insert syntax error

Question

I am trying to write a trigger to remove a certain number of rows when the table hits a limit. This is what I have currently:

IF (SELECT COUNT(rowa) FROM tableA ) > 10

THEN

DELETE FROM table WHERE dateinsert IN (
SELECT * FROM (
    SELECT dateinsert FROM tableA ORDER BY dateinsert ASC limit 1
) AS P
)
end if;

pypmyadmin prompt that i have a syntax error.

Tomas · Accepted Answer · 2016-06-13 09:15:53Z

1

You have syntax error in your sql DELETE statement. You cannot use the keyword table. Below is an example.

I have changed table to tableA in the delete statement.

IF (SELECT COUNT(rowa) FROM tableA ) > 10 THEN
  DELETE FROM tableA WHERE dateinsert IN 
   (SELECT dateinsert FROM
    (SELECT dateinsert FROM tableA ORDER BY dateinsert ASC limit 1) AS tmp1);
END IF;

edited Jun 13, 2016 at 9:15

answered Jun 13, 2016 at 7:03

Tomas

1384 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kenAu89 Over a year ago

Hi Tom, thanks. But I encountered the following error: This verison of MariaDB doesnt yet sypport LIMIT, IN/ALL,ANY/SOME subquery

Tomas Over a year ago

Hi Ken, sorry about that. I have updated the answer.

kenAu89 Over a year ago

Hi Tom, still having error at "syntax near end if". Separately, the query and subquery is working well when executed. Let me try to work on it. Thanks again.

Tomas Over a year ago

There was a missing semicolon at the end of the DELETE statement, just before END IF.

kenAu89 Over a year ago

Hi Tom, thanks again. able to insert the trigger. On a side note, it encounter "Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger" when the row hit 10. I assumed that the table cant be updated as it is locked by the trigger. do you have any work around for this?

Collectives™ on Stack Overflow

mysql trigger delete on insert syntax error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related