I'm trying to run a delete from a very large table using the primary key id and I'm thinking if its more appropriate to create a temporary table to store all my IDs or to create a cursor to store all my IDs. I also need help to write the loop to process the IDs and delete them one by one followed by a commit.
So far I have created the following temporary table:
CREATE TEMPORARY TABLE challs_to_delete (chall_id VARCHAR(22));
INSERT INTO challs_to_delete (chall_id) SELECT IX_RC_ID FROM IX_RCS where DTM < '2024-04-02 01:00:00.000'AND STATUS IN ('AP','AZ','DF','PL');
I thought I had my delete statement:
DELETE FROM IX_RCS WHERE IX_RC_ID IN (SELECT chall_id FROM challs_to_delete WHERE chall_id NOT IN (SELECT IX_RC_ID FROM IX_RCS) LIMIT 1000);
but realise now that LIMIT is not supported in an IN sub-select.
I'm struggling to put it all together in a loop and I'm not sure if the cursor method would be better and more targeted to delete row by row but I don't know how to write it.
Please help.