Remix.run Logo
aurareturn 7 months ago

  DELETE FROM films;
I'm surprised databases makes it so easy to just delete an entire table. I think the command should be

  DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;
magicalhippo 7 months ago | parent | next [-]

Agreed, I've long been thinking that DELETE should require a WHERE clause.

If you really want to just delete everything, just do WHERE 1=1.

yen223 7 months ago | parent | next [-]

It would be nice if there was a psql option that would warn you if you're about to do any kind of update, delete or insert without doing a BEGIN;

(You could always set autocommit=false, which forces you to explicitly commit or rollback every action, but that has its own baggage)

buro9 7 months ago | parent | prev | next [-]

UPDATEs should require a WHERE clause too.

At which point we could just say all SQL should have a WHERE clause.

jaredsohn 7 months ago | parent | next [-]

I think SELECTs without WHERE clauses are fine

marcosdumay 7 months ago | parent [-]

But requiring them is fine too. Even more on Postrges, that has booleans.

ahoka 7 months ago | parent | prev [-]

And queries should start with WHERE.

awestroke 7 months ago | parent [-]

FROM, then WHERE, then SELECT

Jabbles 7 months ago | parent | next [-]

Pipe Syntax In SQL

https://research.google/pubs/sql-has-problems-we-can-fix-the...

https://news.ycombinator.com/item?id=41338877

7 months ago | parent | prev | next [-]
[deleted]
hamandcheese 7 months ago | parent | prev [-]

This is the way.

7 months ago | parent | prev [-]
[deleted]
RedShift1 7 months ago | parent | prev | next [-]

There is pg-safeupdate: https://github.com/eradman/pg-safeupdate

jsemrau 7 months ago | parent | prev | next [-]

MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.

evanelias 7 months ago | parent | next [-]

You're probably thinking of the --safe-updates option [1] for the `mysql` CLI, also available as the memorable alias --i-am-a-dummy. This requires UPDATE and DELETE to have either a WHERE clause or a LIMIT clause. Under the hood, the command-line client option just manipulates the sql_safe_updates session variable [2] to enforce the UPDATE and DELETE requirement, as well as a couple other unrelated variables to prevent overly-huge SELECTs.

It's not enabled by default out of the box, but some companies do override their configuration to enable it for new sessions, iirc Facebook did this.

[1] https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html#safe...

[2] https://dev.mysql.com/doc/refman/8.4/en/server-system-variab...

physicsguy 7 months ago | parent | prev | next [-]

That just wouldn’t fly where you have business customers, many insist on data deletion at the end of contracts. In practice though partitioning or using seperate databases can be a better strategy for dealing with that as otherwise dealing with backups are challenging.

kijin 7 months ago | parent | prev [-]

It might depend on the version, but last time I checked, DELETEing an entire table was much slower than TRUNCATE TABLE.

eddd-ddde 7 months ago | parent [-]

I'm pretty sure that only applies to Postgres.

magicalhippo 7 months ago | parent [-]

Sybase SQLAnywhere as well, and not unlikely MSSQL too given its shared ancestry.

Delete with WHERE is sufficiently slow in MSSQL we have to do batched deletes, but I can't recall offhand if that holds for whole table deletion as well.

7 months ago | parent | prev | next [-]
[deleted]
7 months ago | parent | prev | next [-]
[deleted]
cruffle_duffle 7 months ago | parent | prev | next [-]

That is why you always get in the habit of wrapping your stuff in “BEGIN TRANSACTION”. Then if and when you fuck up you can issue a rollback and be all good.

lowbloodsugar 7 months ago | parent | prev [-]

I’ve deleted the internet??