How to keep on transaction when it fails for some rows?

Question

Hello!

I want that when I execute a query for example DELETE FROM Contact, and an error is raised during the transaction it should delete the rows that are able to be deleted raising all the relevant errors for the rows that cannot be deleted.

Answer 1

If you're using MySQL you can take advantage of the DELETE IGNORE syntax.

Answer 2

This is a feature which will depend entirely on which flavour of database you are using. Some will have it and some won't.

For instance, Oracle offers us the ability to log DML errors in bulk. The example in the documentation uses an INSERT statement but the same principle applies to any DML statement.

Answer 3

For SQL Server you are not going to break the atomicity of the Delete command within a single statement - even issued outside of an explicit transaction, you are going to be acting within an implicit one - e.g. all or nothing as you have seen.

Within the realms of an explicit transaction an error will by default roll back the entire transaction, but this can be altered to just try and rollback the single statement that errored within the overall transaction (of multiple statements) the setting for this is SET XACT_ABORT.

Since your delete is a single statement, the XACT_ABORT can not help you - the line will error and the delete will be rolled back.

If you know the error condition you are going to face (such as a FK constraint violation, then you could ensure you delete has a suitable where clause to not attempt to delete rows that you know will generate an error.