CREATE OR REPLACE PROCEDURE cleanup_rrs() AS
DECLARE _row_count int = 1;
BEGIN
WHILE _row_count > 0 LOOP
DELETE FROM someTable WHERE cTime < date_sub(now(),interval '3' day) LIMIT 20;
_row_count = ROW_COUNT();
DO SLEEP(1);
END LOOP;
END;
This is to delete records with limit and interval. But i got SQL syntax error here. I tried DO SLEEP(1), CALL SLEEP(1), or SLEEP(1) but all failed.
I also tried to use ‘select SLEEP(1)’, but got: Feature ‘Non-ECHO SELECT inside stored procedure as statement’ is not supported by MemSQL.
CREATE OR REPLACE PROCEDURE cleanup_rrs() AS
DECLARE _row_count int = 1;
BEGIN
WHILE _row_count > 0 LOOP
DELETE FROM someTable WHERE cTime < date_sub(now(),interval '3' day) LIMIT 20;
_row_count = ROW_COUNT();
ECHO SELECT SLEEP(1);
END LOOP;
END;
And if you don’t want to output the result of “SELECT SLEEP(1)” then instead of using ECHO SELECT, you can use “SELECT SLEEP(1) into variableName” and just ignore the result that is put into variableName.