0

I have to delete 1000 rows in IN clause. I wrote a query which contains multiple id's in the WHERE condition. If any row is throwing a foreign key reference error, the statement should continue on executing.

DELETE FROM CUSTOMERS 
WHERE ID IN (1, 2, 3, 4)

If ID = 3 IS referenced by another table, it should not be deleted, but ID = 4 has to deleted.

I tried SMO execute query method with execution type continue on error. The script is not throwing error in code, but none of the data is being deleted.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohan R
  • 1
  • 2
    SQL does not perform the partial execution of the query. If the number of the referenced table are known and if they are not many, why no try deleting the data from them first using the same IN clause and then delete data from Customers table? – Chetan Feb 15 '23 at 02:23
  • https://stackoverflow.com/questions/39911531/t-sql-continue-query-execution-on-errors – Chetan Feb 15 '23 at 02:26
  • Other option is to execute delete statement for each customer id in separate SQL query and wrap the execution inside try catch block. Log the error in the catch block and move on – Chetan Feb 15 '23 at 02:27
  • 3
    Just add a `where not exists (select from OtherTable where id = fk)` – Dale K Feb 15 '23 at 02:33
  • 1
    @DaleK the [comment](https://stackoverflow.com/questions/75454893/delete-multiple-rows-in-sql-without-throwing-error-to-continue-on-next-record-i/75459284#comment133142132_75460253) I've left on Joel's answer is also relevant to your proposed solution. For more information, read [Dan Guzman's blog post](https://weblogs.sqlteam.com/dang/2007/10/28/conditional-insertupdate-race-condition/) about race conditions or my self-answer [here](https://stackoverflow.com/a/48741905/3094533). this is the same problem with conditional insert. – Zohar Peled Feb 15 '23 at 13:41
  • If I'm deleting 1000 records in foreach loop, Performance is very slow compared with IN clause. Using IN clause I can delete all record with in mins. But loop takes time – Mohan R Feb 15 '23 at 16:55
  • Actually I'm doing archival. Delete queries dynamically constructed in c#. So I ha e to find the referenced table dynamically and have to add it in dynamic query. If table have multiple tables I have to prepare NOT EXISTS condition to all referenced tables – Mohan R Feb 15 '23 at 16:58
  • 1
    @ZoharPeled while you are correct, depending on the use-case the not exists might still be a viable solution. For example if OP is only running this delete rarely and from a single connection then it will be fine. – Dale K Feb 15 '23 at 20:51
  • 1
    @DaleK I agree. – Zohar Peled Feb 15 '23 at 22:01

2 Answers2

1

You can't have a partial execution of a delete statement.
However, SQL Server supports joins in delete statements, so the correct way to delete these records would be something like this:

DELETE C
FROM CUSTOMERS As C
LEFT JOIN RelatedTable As R 
    ON C.Id = R.Id
WHERE C.Id IN (1, 2, 3, 4)
AND R.Id IS NULL
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

Zohar Peled has given an excellent answer to OP's specific situation (+1). I will add that for a broader case of having to delete records using complex rules that may be too difficult to express in a single select you have a couple of other options that you could consider.

They both involve collecting up the primary keys of the records you want to delete in multiple steps.

  1. You can create a temporary table that holds a list of the primary key values that you want to delete, fill it with as many selects as you need and then delete where the key is in the sub-select from the temporary table.

  2. You can avoid the temporary table and delete from a sub-select that is made up of a series of selects aggregated by union all clauses.

Do it the way Zohar Peled indicated if you can. If that is impractical for some reason, you have a couple of other options to get the same result.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • 1
    Note that by separating the queries to collect the keys for deletion and the delete statement you're exposed to potential errors due to a race condition between your execution and other, unrelated updates or insert on dependent tables. This risk can be minimized using explicit transactions with `SERIALIZABLE` lock (read [SET TRANSACTION ISOLATION LEVEL](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16) for details) but that means a relatively long lock for all tables involved. A join really is the simplest way to avoid it – Zohar Peled Feb 15 '23 at 13:36
  • @ZoharPeled you are correct. There are always things to consider and data stability is a very important consideration if you're going to decompose a complex process into multiple steps. Long or escalating locks are another. It will depend on the specific circumstances. As I noted in my answer, I agree that your approach is the best for the OP's specific scenario. My answer is intended for other people who might come across this question in the future who are looking for an answer to something related, but different. – Joel Brown Feb 15 '23 at 22:18
  • Of course, for many scenarios, braking apart a complex process to smaller steps is the correct thing to do. I just thought that since your answer didn't contain any warnings about race conditions, lock escalations and such it would be a good idea to leave a comment about it. – Zohar Peled Feb 16 '23 at 06:21