Skip to content
Advertisement

How to select a record if the query returns one row, or select no record if the query returns more rows?

I require to select a row if there is only one row exists, if there are more rows, it should select 0 rows.

Advertisement

Answer

If you’re using PL/SQL, then selecting the column using select-into will throw a too_many_rows exception if there’s more than one row returned:

If you want to do this just using SQL, then you can do something like:

UPDATE

As DazzaL says in the comments, the reason for the rownum <= 2 restriction is to short-circuit the query if there’s more than 2 rows in the result set. This can give significant performance benefits if the dataset is large.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement