3 ms·
SQL really sucks. DELETE without WHERE should be a syntax error. Use `where id=id` if you really want everything gone.
by awestroke 3y ago
SQL really sucks. DELETE without WHERE should be a syntax error. Use `where id=id` if you really want everything gone.
- paulryanrogers 3y agoCould probably be done with a DB statment-level trigger. It's sometimes useful to flush tables for testing or work tables. TRUNCATE is even a special case command for such purposes.
- crazygringo 3y agoBecause of this, it's also customary to use a LIMIT just in case. For something like this, LIMIT 3 if you assume the query is correct (e.g. in production), or LIMIT 4 if you're running it manually -- so that if it reports 4 rows changed instead of 3, you know you messed up somewhere, but at least you only have up to 4 rows to fix by hand, rather than an entire table.
- AntonZ234 3y agoI’m used to using limits purely in select queries, honestly I didnt event know its applicable for update queries. Still, it would probably have been after the where, and ignored too. not using transactions is probably my biggest mistake here.
- mlyle 3y agoOf course, the LIMIT is no good here because the correctly-written statement is truncated.
- cortesoft 3y agoWould not have helped, since the limit would have applied to the second statement in this case. The issue wasn’t the statement, it was the tool thinking blank line == end ststement.
- syslog 3y agoA decent database has transactions, so you can rollback your fat-fingered queries.
- cafard 3y agoPostgres and SQL Server have always had transactions; but unless you frame your DML in BEGIN TRANSACTION/[COMMIT|ROLLBACK], each statement is committed as executed. If you broke in on Oracle, where you must explicitly commit statements (unless your client sets up auto-commit), this can take some getting used to.
- crazygringo 3y agoYeah, I'd definitely be in favor of a flag that didn't allow DELETE without WHERE. Too easy to accidentally hit enter before you finish writing the query, in certain circumstances. Disable the flag if you want pure SQL. And just use "WHERE true" otherwise.
- maxbohun 3y agoIt shouldn’t be error, but it has to warn you..are u sure to execute this query?…(by default)
- awestroke 3y agoWhy not an error? How often do you truncate a table? Often enough that you can’t afford to type ` where true `?
- Karellen 3y agoBut... this query used UPDATE orders SET is_deleted = true DELETE without WHERE wasn't part of the problem.
- awestroke 3y agoGood idea, let’s enforce WHERE on UPDATE as well