Oninit
Talk to an expert →

Informix Error -365

-365 Cursor must be defined on simple SELECT for FOR UPDATE.

The cursor named in this statement (probably an OPEN statement) was declared with the FOR UPDATE clause. However, it has been associated with a SELECT statement that joins two or more tables or that uses UNIQUE, DISTINCT, GROUP BY, ORDER BY, FOR READ ONLY, INTO TEMP, or UNION, INTERSECT, or MINUS. Such a SELECT statement cannot be used in an update, because there is no way to distribute the new data back into the multiple tables.

Review the declaration of the cursor. If it is declared FOR statement id, also review the PREPARE statement that set up that statement. You might need two cursors, one for general queries and another specifically for updating.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-365 fires when a FOR UPDATE cursor is declared over a SELECT that's too complex to map back to its source table unambiguously — a multi-table join, or a query using UNIQUE, DISTINCT, GROUP BY, ORDER BY, INTO TEMP, or a set operation (UNION/INTERSECT/MINUS). Any of these can produce a result row that no longer corresponds cleanly to exactly one row in exactly one source table, so there's no well-defined target for UPDATE ... WHERE CURRENT OF.

  1. A FOR UPDATE cursor declared over a multi-table join — the server can't determine which table's row an update through the cursor should apply to.
  2. A FOR UPDATE cursor over a query using GROUP BY/aggregates — grouped/aggregated rows don't correspond to a single source row at all.
  3. A FOR UPDATE cursor over DISTINCT/UNIQUE — de-duplication can collapse multiple source rows into one result row, breaking the one-to-one mapping FOR UPDATE needs.
  4. A FOR UPDATE cursor over a UNION/INTERSECT/MINUS — the combined result doesn't map to any single source table.
Solutions / Resolution
  1. Simplify the cursor's SELECT to a single-table query if updates through the cursor are genuinely needed, per the official guidance.
  2. Use separate cursors for querying versus updating: a read-only cursor (or FOR READ ONLY) for the complex query, and a distinct, simple single-table FOR UPDATE cursor — driven by a key value obtained from the first — to perform the actual update.
  3. If the complex query's purpose is filtering which rows to update, capture the qualifying keys first, then drive a simple FOR UPDATE cursor per key.
Examples
Splitting a complex query from the update
-- Won't work directly — a join can't back a FOR UPDATE cursor:
DECLARE curs1 CURSOR FOR
    SELECT o.order_id, o.status FROM orders o, customers c
    WHERE o.customer_id = c.customer_id AND c.region = 'West'
    FOR UPDATE;
-- -365

-- Fix: read-only cursor for the complex query...
DECLARE curs_find CURSOR FOR
    SELECT o.order_id FROM orders o, customers c
    WHERE o.customer_id = c.customer_id AND c.region = 'West';

-- ...driving a simple single-table FOR UPDATE cursor per row
DECLARE curs_upd CURSOR FOR
    SELECT status FROM orders WHERE order_id = ? FOR UPDATE;
Diagnostic Checks
  1. Check the cursor's SELECT for joins, GROUP BY, DISTINCT/UNIQUE, INTO TEMP, or set operations — any of these disqualifies it from FOR UPDATE.
  2. Confirm the query is a genuinely simple, single-table SELECT if FOR UPDATE is required.
Related Errors / Related Topics
  • -364 — "Column column-name not declared for UPDATE OF." A related FOR UPDATE-cursor restriction, about column scope rather than query complexity.
  • -290 — "Cursor not declared with FOR UPDATE clause." Another related cursor-update restriction, about the clause being missing entirely.

Split complex, multi-table, or aggregated queries into a read-only lookup step plus a separate simple single-table FOR UPDATE cursor for the actual modification.