4 ms·
SQL needs to make you add a where clause to update and delete. If you really want to delete or update everything, then you should have to state it like this:
by forinti 2y ago
SQL needs to make you add a where clause to update and delete.
If you really want to delete or update everything, then you should have to state it like this:
delete all rows from customers;
update all rows from items set price=0;
- mrkeen 2y agoI wouldn't mind if SELECT was guaranteed read-only as well. (rename SELECT INTO).
- acimim_ha 2y ago"SELECT * INTO table" is not ANSI standard.
- 24t 2y agoI find myself caught out more often by incomplete where clauses — paranoid dry runs with rollbacks have saved me more than once. Also fwiw DataGrip asks for confirmation before running an update or delete without a where clause
- est 2y agoOr add a mandatory `where` for delete and update queries. To delete/update all, add `where 1=1` -------------------------------- p.s. In mysql I always add `limit 1` for every update. If `limit 99999` isn't enough you are probably doing it wrong.
- jandrese 2y agoOr just allow "limit unlimited" for those cases where you are working with lots of rows.
- chuckadams 2y agoJetbrains IDEs add a warning to updates or inserts without a "where" clause, and asks for confirmation when you try to run them.
- tomrod 2y agoMost systems I work in raise a warning when an update or insert is missing a where clause. For all rows, we typically just use `where 1=1` to encompass all rows. Allows for cleaner readability too as each clause can be separated by an AND
- petepete 2y agoMySQL has (or at least had) --i-am-a-dummy-mode which means you have to specify the rows or include a limit. https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe-updates https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe...
- jimbob45 2y agoHad no idea this existed before this morning. T-SQL devs, if you're out there, please add something like this to MSSQL.
- ZiiS 2y agoDon't think it needs a new syntax, just that the WHERE clause is mandatory: DELETE FROM customers WHERE TRUE;
- tonnydourado 2y agoMy first thought is that "where TRUE" is just a wonky thing to have to write, but on further reflection, maybe that's kinda the point. Where-less deletes should be harder to write, making it look stupid will probably help.
- masto 2y agoI spent a past life in a world where it was deemed necessary to perform this kind of interactive database surgery, and I used two techniques to try to mitigate the risk: I had my psql client set to disable autocommit (some people fear this because of leaving locks around, but you pick your poison), and my default user didn't have write access. Neither are perfect, but I ended up not being the person who accidentally deleted a whole table because, as they had explained to me with an annoying "I'm so macho" attitude, they always typed the semicolon first.
- dools 2y agoI had some similar strategies: 1) Write the WHERE clause first 2) Run a SELECT on that WHERE clause 3) Edit the query to make it either update or delete as required Similarly, I only ever perform recursive filesystem commands using find and xargs. I run the find first, then hit the up arrow and pipe it to whatever I want to do (I learned that one from a very patient sysadmin after I accidentally chmod'd -R / 664)
- arscan 2y agoBeen a few decades, but I was in a similar situation and came up with a similar approach. Run the select first and then swap in an update or delete. If the query was somewhat involved to determine what to delete, I’d write the select query to just select row IDs of rows for deletion (assuming a simple primary key), then wrap that with a “delete from table where ID in (select ID from …complex query…)”.
- xnorswap 2y agoI agree preventing DELETE and UPDATE without predicates would be a useful server configuration. And you don't even need special syntax, it just forces someone to write DELETE * FROM [customers] WHERE 1=1 to satisfy the need for a predicate.
- bazoom42 2y agoHow about having sql statements default to “dry run”, only executing if an additional keyword (like “execute”) is added.
- byteknight 2y agoThat becomes a non-boundary because you'd write it every time.
- pak9rabid 2y agoIsn't this something that explicit transactions (BEGIN/COMMIT/ROLLBACK) do? Many DB clients can be configured to have autocommit disabled, so that you're forced to either COMMIT or ROLLBACK each transaction.
- cess11 2y agoThe difference in impact between DELETE * FROM tbl and DELETE * FROM tbl WHERE id > 1 isn't enough to warrant a change to the standard in my opinion. Data loss and corruption risks need other forms of mitigation and they'll cover the unconstrained cases as well.
- Sakos 2y agoI don't think this is something that can be solved at the language level. What's missing is a semantic, decently intelligent interface that can ask "hey, this is gonna delete everything in the table, is that what you wanted?" and it feels like we're slowly moving in that direction with the recent developments in AI.
- randomdata 2y agoThe original relational calculus, created by the same person who wrote the linked paper, did not allow unbounded deletes. One has to wonder how SQL, which was based on his work, managed to screw things up so badly. These were already solved problems before SQL was created.
- ziml77 2y agoI have Red Gate's SQL Prompt extension installed in SSMS. If I try to execute a delete or update without a where clause, it asks me to confirm that's what I want to do. It also clearly indicates in the editor that there's a problem. If I'm sure that's what I want to do and I want to avoid being warned I just add WHERE 1 = 1 to the end. It's a nice feature that adds a little bit of comfort (though I'm still wary enough to hesitate before running SQL data modifications against production)