Oninit
Talk to an expert →

Informix Error -505

Cause and resolution

Number of columns in UPDATE does not match number of VALUES.

This UPDATE statement uses a SET clause in which a list of column names is set equal to a list of expressions or to a SELECT statement. The number of columns on the left of the equal sign is not the same as the number of data values produced on the right. Roll back the current transaction. Then rewrite this statement. If a list of expressions is used, check the punctuation to ensure that each expression is distinct. If a SELECT statement is used, review it to see how many columns it returns.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-505 is a column-count mismatch in an UPDATE ... SET clause: the number of column names on the left of the equal sign doesn't match the number of values (or the number of columns a SELECT subquery returns) on the right.

  1. A miscounted column list against a value list, in the multi-column form SET (col1, col2, ...) = (val1, val2, ...) — the direct, common cause.
  2. A SELECT subquery on the right-hand side returning a different number of columns than the column list on the left expects.
  3. Punctuation errors in a list of expressions — a missing or extra comma that merges or splits what should be distinct value expressions, per the official guidance.
  4. Generated UPDATE statements built from a column list that drifted from the values list after a schema change, without regenerating both together.
Solutions / Resolution
  1. Roll back the current transaction, per the official guidance.
  2. Rewrite the statement, per the official guidance. For a list of expressions, check the punctuation to ensure each expression is distinct and count matches the column list.
  3. For a SELECT-based UPDATE, review how many columns the SELECT actually returns, per the official guidance, and adjust either the SELECT's column list or the SET's column list so they match.
Examples
Mismatched list lengths
UPDATE orders SET (status, ship_date) = ('shipped', TODAY, 'UPS');
-- -505: three values for two columns
Corrected
UPDATE orders SET (status, ship_date) = ('shipped', TODAY);
A SELECT subquery returning the wrong column count
UPDATE orders SET (status, ship_date, carrier) =
  (SELECT status, ship_date FROM staging_orders WHERE order_id = orders.order_id);
-- -505: SET names three columns, the SELECT returns two
Diagnostic Checks
  1. Count the column list and the value list (or SELECT's output column count) side by side before running the statement, especially after a schema change that added or removed a column.
Related Errors / Related Topics

No closely related error codes are cross-referenced for -505 in this set yet.

A straightforward list-length mismatch in the SET clause — recount the columns against the values (or the SELECT's output) before rewriting.