1

I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when more then one row matches?

Using LIMIT and ORDERBY allows the operation to continue, rather than doing a hard abort. Meaning multiple objects may be subject to DELETE with no chance to recover.

Preferably whatever signal results would be distinct from having zero match.

  • 1
    You may try to use your query in a subselect in a select clause. As a subquery in a select clause should be scalar, it will raise an error if you get more than one row (or more than one column)... Or you may control use a PL/SQL function/procedure and raise an error if you get more than one row... – Arkhena May 15 at 11:56
0

As mentioned by Arkhena, use a scalar subquery to determine the row to access:

DELETE FROM MyTable
WHERE ID = (SELECT ID
            FROM MyTable
            WHERE ...whatever ...);

If the subquery returns more than one ID, the query fails. This requires some unique key (here: ID).


If you do not want to get an error from the query, you can use CTE for the filter, and then check the number of rows in the CTE:

WITH r AS (
  SELECT ID
  FROM MyTable
  WHERE ...whatever...
)
DELETE FROM MyTable
WHERE (SELECT count(*) FROM r) = 1
  AND ID IN (SELECT ID FROM r)
RETURNING ID;

This statement will return an ID value only if one row was actually deleted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.