6 ms·
I've often thought the WHERE clause should be required. You could just say WHERE 1=1 if you really meant all rows.
by kogus 6y ago
I'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]