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?
>Solution :
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.