Question 298344 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: need to delete several hundred thousand rows using IDs in text file

I have a text file with several hundred thousand primary IDs of rows I need to delete from a MySQL database of ~20m rows. What’s the best way to do this?

Tags:

2

Answers


  1. 0

    I guess that you can copy and paste all your ID’s that you want to delete on the Notepad++, separete by ”, ” (e.g. –> (‘1’, ‘2’)) and after that, run a Query like this one:

    delete FROM test where id in (‘1’, ‘2’, ‘3’, ‘5’, ’35’);

    This should works.
    Seeya,,,

    Login or Signup to reply.
  2. 0

    Use LOAD DATA INFILE to load the text file into a temporary table. You can then join this table with the table you want to delete from.

    CREATE TEMPORARY TABLE delete_ids (id INT);
LOAD DATA INFILE '/path/to/ids.txt' INTO delete_ids;
ALTER TABLE delete_ids ADD INDEX (id);

DELETE t
FROM your_table AS t
JOIN delete_ids AS d ON t.id = d.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search