MySQL Forums
Forum List  »  Newbie

How to delete millions of record in a loop
Posted by: pandu hondu
Date: November 16, 2016 06:56AM

Hello MYSQL DB Guru's,

I am having issue with regards to the following script.

use mydb;
CREATE PROCEDURE delete_table_incrementally
MODIFIES SQL DATA
BEGIN
REPEAT
DELETE FROM mytable;
LIMIT 3;
UNTIL ROW_COUNT() = 0
END REPEAT;
END;


mysql> source /home/jim/ms_15_delete_table.sql
Database changed
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODIFIES SQL DATA
BEGIN
REPEAT
DELETE FROM mytable' at line 2
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 3' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNTIL ROW_COUNT() = 0
END REPEAT' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
mysql>

Once I get Ok out with source file then I need to execute as below?
./my_query.sql ??



I want to test the script works well on small dummy table before I hit mega table which has millions of records. Also are there any performance or table lock & deadlock considerations ?

Any help would be greatly appreciated.

Cheers
Jim

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.