7 ms·
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 h
by grossvogel 6y ago
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.
- tatersolid 6y agoFor 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.
- mjevans 6y agoIIRC (without checking the manuals) data-definition commands might not be covered by such transactions: such as altering, dropping tables and possibly truncates.
- andreareina 6y agoPostgreSQL 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.
- tatersolid 6y agoDDL is transactional in Microsoft SQL Server as well.
- maynman 6y agoI do the same thing. I also keep auto commit off and make sure the rows updated looks correct before committing the change.