kenAu89 kenAu89 - 5 months ago 10
MySQL Question

mysql trigger delete on insert syntax error

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.

Answer

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;
Comments