Oninit
Talk to an expert →

Informix Error -223

-223 Duplicate table name table-name in the FROM clause.

The table name appears twice in the list that follows the word FROM. Review the statement to see if you intended to name some other table the second time. If you intended to join a table to itself, use a table alias for the second and subsequent instances of the table. The following example shows one way to find customers with the same last name:

SELECT main.lname, main.customer_num, sub.customer_num FROM customer main, customer sub WHERE main.lname = sub.lname AND main.rowid != sub.rowid

When you use table aliases (the words main and sub in the example), the table may be selected from two or more times.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-223 means the same table name appears twice in a FROM clause. Two very different situations produce this: a genuine mistake, or a legitimate self-join missing the aliases it needs.

  1. A table name accidentally listed twice — a copy-paste error or a typo where the same table was intended once but appears twice.
  2. An intentional self-join without aliases. Joining a table to itself — finding employees who share a manager, or customers with the same last name — is a common, legitimate pattern, but it requires table aliases to distinguish the two references; without them, it's indistinguishable from an accidental duplicate.
  3. Auto-generated SQL from a query builder that doesn't properly handle self-join scenarios, emitting the same table name twice without aliasing.
  4. A multi-line JOIN where a table already referenced elsewhere (a subquery, for instance) gets accidentally repeated in the outer FROM clause.
Solutions / Resolution
  1. If the duplicate was accidental, correct the statement to reference the actual intended table instead.
  2. If intentionally self-joining, use table aliases to distinguish each reference to the same table.
  3. For auto-generated SQL, ensure the query-building logic properly aliases tables whenever a self-join scenario is detected, rather than emitting the bare table name twice.
Examples
The accidental duplicate
SELECT * FROM orders, orders WHERE orders.status = 'pending';
-- -223: orders listed twice by mistake

Fix — reference the actually-intended second table:

SELECT * FROM orders, customer WHERE orders.customer_id = customer.id;
The intentional self-join, fixed with aliases
-- Find customers sharing the same last name
SELECT a.customer_id, b.customer_id, a.last_name
FROM customer a, customer b
WHERE a.last_name = b.last_name AND a.customer_id < b.customer_id;

Aliasing the same table as a and b makes the self-join valid — without the aliases, this would be exactly the -223 condition.

Diagnostic Checks
  1. Review the FROM clause for the same table name appearing more than once.
  2. Determine intent — an accidental duplicate needing correction, versus an intentional self-join needing aliases.
Related Errors / Related Topics
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.
  • -217 — "Column column-name not found in any table in the query." Another common, everyday query-construction error in the same general category.

If a self-join is genuinely intended, aliasing both references is the fix — this isn't an error to work around, just a syntax requirement for that pattern.