Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why hasn't SQL Server made a WHERE clause mandatory by default?

It seems like a no brainer to me. I've heard countless stories about people forgetting the WHERE clause in an UPDATE or DELETE and trashing an entire table. I know that careless people shouldn't be issuing queries directly and all that... and that there are legitimate cases where you want to affect all rows, but wouldn't it make sense to have an option on by default that requires such queries to be written like:

UPDATE MyTable SET MyColumn = 0 WHERE *

Or without changing the language,

UPDATE MyTable SET MyColumn = 0 WHERE 1 = 1 -- tacky, I know
like image 976
Josh Avatar asked May 17 '09 00:05

Josh


People also ask

Is WHERE clause mandatory in SQL?

WHERE clauses are not mandatory clauses of SQL DML statements, but can be used to limit the number of rows affected by a SQL DML statement or returned by a query. In brief SQL WHERE clause is used to extract only those results from a SQL statement, such as: SELECT , INSERT , UPDATE , or DELETE statement.

Is the WHERE clause mandatory?

The WHERE clause is mandatory when using the SELECT command.

Can you have a SQL statement without a WHERE clause?

The UPDATE statement in SQL is used to update records in the table. We can modify one or multiple records (rows) in a table using UPDATE statement. If you do not use WHERE clause in UPDATE statement, all the records in the table will be updated.

What happen if WHERE clause is not given in query?

If the given condition does not match any record in the table, then the query would not return any row.


1 Answers

Because the spec doesn't require it, and you shouldn't be running ad hoc sql directly against production data anyway.

like image 127
Joel Coehoorn Avatar answered Oct 06 '22 00:10

Joel Coehoorn