SQLCODE -811: More than one row returned

What it means

A singleton SELECT, written as SELECT ... INTO :host-variables, can return at most one row. A subquery used with =, <, > or another basic comparison has the same limit, because its result is compared with a single value. The same applies to a subselect in the SET clause of an UPDATE. When Db2 finds a second qualifying row, it stops and returns -811.

The SQL itself is valid. The trouble is that the WHERE clause doesn't pick out a single row in the data as it is today. Either the program assumed a key was unique when it isn't, or the table now holds duplicates the design never allowed for.

-811 doesn't roll anything back. Only the failing statement is affected.

Common causes

• The WHERE clause uses only part of the key, for example the customer number without the address type, and the customer now has two addresses.
• The table has no unique index on the columns in the WHERE clause, so nothing stopped duplicate rows from being added.
• A subquery such as WHERE RATE = (SELECT RATE FROM ...) returns several rows for some keys.
• Date-range rows, such as price or rate history, overlap, so two rows are valid on the same date.
• A join in the SELECT INTO multiplies rows, because the joined table has several matches for one key.

How to find the problem

1. Run the same SELECT in SPUFI or QMF with the values from the failing record, leaving out INTO. You'll see every row it returns.
2. Compare those rows to see which column differs. That column is missing from the WHERE clause, or it shows where the bad duplicate data is.
3. Check the table's indexes in SYSIBM.SYSINDEXES. UNIQUERULE D means duplicates are allowed. The key columns are in SYSIBM.SYSKEYS.
4. For a subquery, run it on its own with the outer value typed in.

How to fix it

• If the duplicates are bad data, remove them and add a unique index so they can't come back.
• If several rows are legitimate, make the WHERE clause specific enough to pick one, for example by adding the missing key column or a date test.
• If any one of the rows will do, add FETCH FIRST 1 ROW ONLY. If it matters which row you get, add an ORDER BY as well. Db2 for z/OS accepts both on a SELECT INTO.
• If you expect many rows, replace the SELECT INTO with a cursor and a FETCH loop.
• To test whether a row exists, use FETCH FIRST 1 ROW ONLY or an EXISTS predicate instead of relying on -811.

What it looks like

POLINQ1 - SELECT POLICY_ADDR FAILED, POLICY P00228741
DSNT408I SQLCODE = -811, ERROR:  THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT
         IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE
         RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE
DSNT418I SQLSTATE   = 21000 SQLSTATE RETURN CODE

Last reviewed 2026-10-12 by the WND Software team. Written in our own words. For the full reference, check IBM’s documentation for your z/OS, CICS or Db2 release.