4 ms·

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 whi

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

harikb 6y ago
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.
deleted 6y ago
[deleted]
krab 6y ago
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.
- cableshaft 6y ago
  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.
verve_rat 6y ago
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.
vlunkr 6y ago
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.
SkyBelow 6y ago
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).
- dsego 6y ago
  I think this is true for mysql, at least for delete.
cbm-vic-20 6y ago
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-options.html#option_mysql_safe-updates https://dev.mysql.com/doc/refman/8.0/en/mysql-command-option...
skymt 6y ago
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 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

harikb 6y ago

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.

deleted 6y ago

[deleted]

krab 6y ago

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.

cableshaft 6y ago
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.

verve_rat 6y ago

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.

vlunkr 6y ago

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.

SkyBelow 6y ago

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).

dsego 6y ago
I think this is true for mysql, at least for delete.

cbm-vic-20 6y ago

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-options.html#option_mysql_safe-updates https://dev.mysql.com/doc/refman/8.0/en/mysql-command-option...

skymt 6y ago

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.