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;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)
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…)”.
https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe...
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.
DELETE FROM customers WHERE TRUE;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
Also fwiw DataGrip asks for confirmation before running an update or delete without a where clause
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.
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)
DELETE * FROM tbl
DELETE * FROM tbl WHERE id > 1
Data loss and corruption risks need other forms of mitigation and they'll cover the unconstrained cases as well.