Do SQL update statements always end in WHERE clauses?-CodePudding

I'm developing a system such that users can input queries into the system and the system verifies the query. When the user enters an update statement, I wish to reuse that WHERE clause for an internal select statement.

For example, let's say the user enters:

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

I'd like to reuse WHERE CustomerID = 1 (most likely by taking the substring) for a system select statement to verify that the recently updated rows are in a valid format:

System enters:

SELECT * FROM Customers
WHERE CustomerID = 1;

Is this safe? Do all SQL statements end in WHERE clauses?

CodePudding user response：

I'd check out the official docs for your specific flavor of SQL, as implementations vary. For instance,

MySQL allows for ORDER BY and LIMIT clauses after UPDATE...WHERE.
Microsoft SQL Server has an optional OPTION clause that can follow WHERE.
Oracle's SQL looks like WHERE is safely the last part of an UPDATE (at least for the version linked here).
PostgreSQL allows for a RETURNING clause.
etc.

Of course, the WHERE clause could be omitted in any, though it's not usually what you'd want to do.

CodePudding user response：

No.

A SQL statement like

UPDATE Customers 
SET ContactName = 'Alfred Schmidt';

will simply update all entries in the table without any restriction on which rows to update in a where clause.

CodePudding user response：

SQL update statements are valid with a Table and column = value. However, it is usually considered bad form to leave off any filter. Also, where isn't the only filter possible there's also having or the on portion of a join.