Oninit
Talk to an expert →

Informix Error -207

-207 Cannot update cursor declared on more than one table.

The cursor that is used in this statement was declared FOR UPDATE but with a SELECT statement that joins two or more tables. This action is not supported; the database server does not know how to distribute update values across multiple tables. If you declared the SELECT statement with the cursor, modify either it or the FOR UPDATE clause. If a program constructed the SELECT statement and associated it with the cursor dynamically, the program should inform its user not to use a multitable SELECT statement in this application. (This message occurs only with Version 4.0 or earlier.)

-207 Cannot declare a SELECT INTO statement FOR UPDATE.

When you declare a cursor, you may not use both the INTO clause and the FOR UPDATE clause. To use this cursor to update selected rows, omit the INTO clause. Instead, use an INTO clause on the FETCH statement (or in 4GL, the FOREACH statement).

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-207 actually covers two distinct, unrelated DECLARE CURSOR restrictions that happen to share the same error code — worth telling apart immediately, since the fix is completely different depending on which one applies.

Scenario A: a FOR UPDATE cursor declared on a join.

  1. A cursor declared FOR UPDATE with a SELECT that joins two or more tables. This isn't supported — the engine doesn't know how to distribute update values across multiple tables through a single cursor's FOR UPDATE mechanism.
  2. An attempt at an "updatable join view" pattern via cursor, not realizing the engine doesn't support updating through a joined cursor directly.

Scenario B: INTO combined with FOR UPDATE on the same cursor declaration.

  1. A cursor declaration using both an INTO clause and a FOR UPDATE clause together. These two can't be combined on DECLARE CURSOR — INTO belongs on the FETCH statement (or the FOREACH statement in 4GL) instead.
  2. Code adapted from a read-only cursor pattern (which correctly used INTO on the declaration) into an updatable one, without moving the INTO clause to where it needs to be for a FOR UPDATE cursor.
Solutions / Resolution

For the multi-table join case:

  1. Restructure to update through a single-table cursor using WHERE CURRENT OF cursor_name against the specific base table that needs updating, rather than trying to update through a joined cursor. If multiple tables genuinely need updates, use separate statements per table.

For the INTO + FOR UPDATE case:

  1. Omit the INTO clause from the DECLARE CURSOR statement, and instead place it on the FETCH statement (or FOREACH in 4GL) when retrieving rows from the FOR UPDATE cursor.

In general:

  1. Review the DECLARE CURSOR statement to determine which of the two conditions actually applies before attempting a fix — they require entirely different changes.
  2. Design updatable-cursor logic around single-table cursors from the start when update-in-place behavior (WHERE CURRENT OF) is genuinely needed.
Examples
Scenario A: a join with FOR UPDATE
DECLARE cur1 CURSOR FOR
  SELECT o.order_id, c.name FROM orders o, customer c
  WHERE o.customer_id = c.customer_id
  FOR UPDATE;
-- -207: FOR UPDATE on a multi-table join isn't supported

Fix — update through a single-table cursor instead:

DECLARE cur1 CURSOR FOR
  SELECT order_id FROM orders WHERE customer_id = :cid
  FOR UPDATE;

FETCH cur1 INTO :oid;
UPDATE orders SET status = 'shipped' WHERE CURRENT OF cur1;
Scenario B: INTO combined with FOR UPDATE
DECLARE cur1 CURSOR FOR
  SELECT order_id INTO :oid FROM orders WHERE status = 'pending'
  FOR UPDATE;
-- -207: INTO can't be combined with FOR UPDATE on the declaration

Fix — move INTO to the FETCH:

DECLARE cur1 CURSOR FOR
  SELECT order_id FROM orders WHERE status = 'pending'
  FOR UPDATE;

FETCH cur1 INTO :oid;
Diagnostic Checks
  1. Review the DECLARE CURSOR statement to determine which of the two conditions applies — a multi-table join combined with FOR UPDATE, or INTO combined with FOR UPDATE.
  2. If a multi-table update was intended, confirm whether it actually needs restructuring into separate per-table statements against single-table cursors.
Related Errors / Related Topics
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

Identify which of the two distinct conditions actually produced this error before attempting a fix — the two scenarios behind -207 have nothing to do with each other beyond sharing this code.