Hacker News Reader

I always do destructive SQL commands in two steps: first run a select using the WHERE clause you intend to use and verify which records will be affected, then hit the up arrow and edit the beginning of the query leaving the WHERE intact.

I also like adding redundant conditions to the WHERE so a typo in any single one of them won't sink me.

View on HN

For the rare but critical manual SQL mod our common safety measure is to wrap every DELETE or UPDATE in BEGIN TRAN...ROLLBACK TRAN first. Run on test systems or snapshots multiple times, checking the result inside the transaction.

Finally, change ROLLBACK to COMMIT only when you are positive all is well.

IIRC (without checking the manuals) data-definition commands might not be covered by such transactions: such as altering, dropping tables and possibly truncates.

PostgreSQL is quite good about DDL being transactional. So I was surprised (tbf, I shouldn't have been) when Redshift autocommitted after a TRUNCATE. But DROP TABLE is transactional, go figure.

DDL is transactional in Microsoft SQL Server as well.

I do the same thing. I also keep auto commit off and make sure the rows updated looks correct before committing the change.

Reply on news.ycombinator.com