3 ms·
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-
by aurareturn 2y 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;
- jsemrau 2y agoMySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.
- physicsguy 2y agoThat 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 2y agoIt might depend on the version, but last time I checked, DELETEing an entire table was much slower than TRUNCATE TABLE.
- eddd-ddde 2y agoI'm pretty sure that only applies to Postgres.
- magicalhippo 2y agoSybase 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.
- evanelias 2y agoYou'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-updates 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-variables.html#sysvar_sql_safe_updates https://dev.mysql.com/doc/refman/8.4/en/server-system-variab...
- RedShift1 2y agoThere is pg-safeupdate: https://github.com/eradman/pg-safeupdate https://github.com/eradman/pg-safeupdate
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- cruffle_duffle 2y agoThat 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 2y agoI’ve deleted the internet??
- magicalhippo 2y agoAgreed, 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.
- buro9 2y agoUPDATEs should require a WHERE clause too. At which point we could just say all SQL should have a WHERE clause.
- jaredsohn 2y agoI think SELECTs without WHERE clauses are fine
- marcosdumay 2y agoBut requiring them is fine too. Even more on Postrges, that has booleans.
- ahoka 2y agoAnd queries should start with WHERE.
- awestroke 2y agoFROM, then WHERE, then SELECT
- hamandcheese 2y agoThis is the way.
- Jabbles 2y agoPipe Syntax In SQL https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/ https://research.google/pubs/sql-has-problems-we-can-fix-the... https://news.ycombinator.com/item?id=41338877 https://news.ycombinator.com/item?id=41338877