Best approach to delete and update a big amout of rows in MySQL

Question

I'm currently developing a REST API that connects to a MySQL database and does a bunch of queries.

In one of those queries I happen to have to delete a bunch of rows from a table and each of those rows have a unique identifier.

And basically I have two approaches to do so: (that I know).

1. Build a bunch of DELETE FROM table WHERE id=value; statements and execute them sequentially.
2. Build only a DELETE FROMstatement but with a massive OR chain.

I don't have a ton of experience handling databases and any suggestion or advice would be greatly appreciated.

EDIT:

I just noticed that I also have something similar happening with a UPDATE FROM statement where I set a lot of records to the same given value, so any advice on that too would be really nice.

Answer 1

Simple and easy:

DELETE FROM tbl
    WHERE id IN ( 11,22,... 999 );

But... If you have thousands of ids to delete, let's discuss where they the ids came from. If they are sitting in another table, or can be found via some form of SELECT, then a "Multi-table DELETE" is probably a better way to do it.

Or if you know that the rows are consecutive in some way, then there may be other options. For example, to delete "old" rows:

DELETE FROM tbl
    WHERE datetime < NOW() - INTERVAL 7 DAY;

will delete rows older than a week.

Answer 2

You can use a JOIN in your DELETE statement. Someone may need to help out with MySQL syntax, but I believe this is ANSI standard SQL that will remove records in TABLE1 that also have a record in TABLE2.

DELETE t1
FROM TABLE1 t1
    INNER JOIN TABLE2 t2 
        ON t1.ID = t2.ID

Ideally, your delete join will just use whatever logic you're already using to determine what records need to be removed. Say you want to remove records from your Orders table where the client associated with the order has been put on a credit hold:

DELETE o
FROM ORDERS o
    INNER JOIN Clients c
        ON o.ClientID = c.ID
WHERE c.CreditHold = 1;

But failing that, you can just put your to-be-deleted IDs into a separate table, join to that, and then clear or delete the temp table.