In may day-to-day job, I often have to create SQL files containing minor changes (no DDL operation usually) in order to migrate those to production databases easily.

The problem is, even with syntax checking, I often run into issues with syntax errors, which I notice only when I try to run said queries.

Even if I wrap those in transactions, insertions and deletions made before the point of the error are performed regardless.

Is there a way to rollback on syntax errors in a transaction?

  • 1
    Maybe the best option is by checking it using a test db – McNets Sep 10 at 8:36
  • You could switch to PostgreSQL. – Laurenz Albe Sep 10 at 8:47
  • @McNets that's what I'm usually doing but it easily becomes tedious because of certain factors. – Zoltán Schmidt Sep 10 at 9:33
  • @LaurenzAlbe how/why would that help? – Zoltán Schmidt Sep 10 at 9:34
  • 2
    In PostgreSQL, DDL is transactional, and any error causes the transaction to be rolled back. But that is off-topic. – Laurenz Albe Sep 10 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.