0

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.

2
  • What is the source of the UIDs? Is that stored somewhere, or user-supplied? Or maybe the user enters a different value, and you want to delete from the table and filter on that field.
    – Jacob H
    Jul 20, 2018 at 22:20
  • The values are provided from the user via UI. Even though, the user cannot see those UIDs. Those ID are generated by the AUTO_INCREMENT option on the MySQL column. EDIT: I also just realised that I have something similar with a UPDATE statement that sets a bunch of records to the same value. Jul 20, 2018 at 22:26

2 Answers 2

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.

1
  • Since you were the one who suggested the IN operator the first I think you're the one who deserves the accepted answer. Anyway, thank you everybody. Jul 23, 2018 at 10:16
0

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.

3
  • DELETE FROM.. FROM is not ANSI, it is 100% T-SQL proprietary, and is a Pandora box of 'hidden bugs', especially for UPDATE. You can easily achieve the same result for DELETE with ANSI syntax: DELETE FROM T1 WHERE ID IN (SELECT ID FROM T2)
    – SQLRaptor
    Jul 20, 2018 at 22:32
  • The IDs come from the API call (user provided) and I cannot use JOIN statements unless I use your last suggestion. And mentioning your last suggestion; isn't even more costy to create a table, fill it, perform a delete with a join and then delete the temporary table? those sound like a lot more operations than really needed. If you could provide some proof that this would have a positive impact performance-wise I'd be down for giving it a try. Jul 20, 2018 at 22:35
  • If the IDs are coming from a front-end application, but they're being sent all at once, then you can use IN: DELETE FROM T1 WHERE ID IN(101, 345, 983). Jul 20, 2018 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.