Hacker News Reader

Comment by dingaling | Hacker News Reader

I wish SQL had a dry-run mode in updates and deletes for that reason.

"Run it as a query first" gets 90% of the way until you drop a constraint by accident whilst rewriting it as an update :o

View on HN

For interactive queries / surgery, you do have an option with a transaction (begin/commit/abort).

If it is Postgres (don't know about other dbs), you can go a way long way using "savepoints" and "rollbacks" to truly have a trial-and-error safe surgery on db. Still dangerous, but quite helpful. I hate working on any other db without those features. Postgres also allows schema changes to be within a txn envelope.

I've thought the same thing. I also wish SET came after where. I've done "UPDATE table_x SET something = true"; and then forgot the WHERE clause.

Transactions and rollback is the dry run. The problem is that if you keep the transaction open for too long, you will block other updates to the same data.

Yep, I always write any update queries as a rollback transaction with some selects inside it to verify what the data looks like after it's done now, before I switch it to commit. I primarily use Microsoft SQL Server right now, so I also use WITH (NOLOCK) to prevent issues running my query will have with other updates.

Enough folks have replied that transactions are the way to go, but I just wanted to add that whatever interface tool you use for your database may have an option to force you to commit your transactions manually. For example PostgreSQL's default 'psql' shell has the "autocommit" option which, when disabled, requires you to manually 'commit;' before any changes take effect.

I think an improvement to SQL would be for insert/update/delete clauses to require a where clause and allow for something like 1=1 if you really intend to hit all rows. A safe but even more invasive would be requiring an end to the were clause as well (to prevent selecting a few but not all constraints).

I think this is true for mysql, at least for delete.

Wrap it in a transaction and roll back the transaction at the end. Then remove the transaction when you are ready to do it for real.

You can jam a select in the end of the transaction to check what happens.

MySQL has a command line option "--i-am-a-dummy" (aka "--safe-updates") for exactly this purpose.

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-option...

Reply on news.ycombinator.com