Hacker News Reader

Comment by awestroke | Hacker News Reader

SQL really sucks. DELETE without WHERE should be a syntax error. Use `where id=id` if you really want everything gone.

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

Of course, the LIMIT is no good here because the correctly-written statement is truncated.

I’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.

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

A decent database has transactions, so you can rollback your fat-fingered queries.

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

Yeah, 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.

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

But... this query used

    UPDATE orders
    SET is_deleted = true

DELETE without WHERE wasn't part of the problem.

Good idea, let’s enforce WHERE on UPDATE as well

It shouldn’t be error, but it has to warn you..are u sure to execute this query?…(by default)

Why not an error? How often do you truncate a table? Often enough that you can’t afford to type ` where true `?

Reply on news.ycombinator.com