4 ms·
First trick shows that update and delete should have a special syntax for when you really mean all rows: UPDATE users SET email = lower(email) This should
by forinti 6y ago
First trick shows that update and delete should have a special syntax for when you really mean all rows:
UPDATE users SET email = lower(email)
This should return an error: "do you mean all rows?"
UPDATE users all rows SET email = lower(email)
Or something like that.
- kogus 6y agoI've often thought the WHERE clause should be required. You could just say WHERE 1=1 if you really meant all rows.
- Izkata 6y agoThis is a settable option in mysql: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_safe_updates https://dev.mysql.com/doc/refman/8.0/en/server-system-variab...
- pmiller2 6y agoThat option is slightly less convenient than just requiring a WHERE or LIMIT clause, because it specifies that the WHERE clause has to have a key constraint. You can still do approximately the same as the 'WHERE 1=1' trick with something like this: UPDATE users SET email = lower(email) LIMIT COUNT(email) if you want. I kind of like that, because it requires you to be more explicit than just putting 'WHERE 1=1'. It seems like mistakes would be fewer, too, because putting LIMIT COUNT(email) isn't just some default boilerplate you can get used to typing.
- uhoh-itsmaciek 6y agoDoesn't that skip counting null emails (though not filtering them for lowercasing), meaning you may not lowercase all emails if you have any null emails?
- NicoJuicy 6y agoNice catch, Google searched it: > Not everyone realizes this, but the COUNT function will only count the records where the expression is NOT NULL in COUNT(expression) . When the expression is a NULL value, it is not included in the COUNT calculations.
- edgarvaldes 6y agoI have seen "WHERE 1" in MySQL land. is it the same thing?
- deleted 6y ago[deleted]
- rrrrrrrrrrrryan 6y agoMany IDEs do throw up an "Are you sure?" confirmation prompt by default if you try to perform a DELETE or an UPDATE without a WHERE clause. I know some even _require_ you to have a WHERE clause by default (it's a toggleable setting, obviously), which is why you'll see some database devs add "WHERE 1=1" to the end of their queries as a matter of habit.