Oninit
Talk to an expert →

Informix Error -360: Cannot modify a table or view that is also used in subquery.

Cause and resolution

Cannot modify a table or view that is also used in subquery.

The UPDATE, INSERT, or DELETE statement uses data taken from the same table in a subquery.

Because of the danger of entering an endless loop, this action is not allowed, except in the case of an uncorrelated subquery in the WHERE clause of the DELETE or UPDATE statement.

To avoid this error, first select the input data into a temporary table, and then refer to the temporary table in a separate DELETE, UPDATE, or INSERT statement.

A subquery with a correlated column name cannot reference in its FROM clause the same table that an enclosing UPDATE or DELETE statement is modifying, as in the following example:

database stores_demo; update orders set ship_charge = ship_charge + 2.00 where customer_num in (select new_order.customer_num from orders as new_order where orders.ship_weight < new_order.ship_weight);

In an SPL routine, even an uncorrelated subquery is not allowed in a DELETE or UPDATE statement that modifies the same table that the FROM clause of the subquery specifies, as in the next example:

database stores_demo; create procedure ship_count(customer_id integer) returning integer define shipcount integer; select count(*) into shipcount from orders where customer_id = orders.customer_num and ship_charge > 20; return shipcount; end procedure;

update orders set ship_charge = ship_charge + 2.00 where customer_num in (select customer_num from customer where customer.state = 'CA' and ship_count(customer.customer_num) > 5);

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-360 guards against a genuinely hazardous pattern: an UPDATE, INSERT, or DELETE statement that also references the same table inside a subquery. The server prohibits this broadly to prevent potential endless loops or undefined behavior from a statement effectively reading and writing the same data simultaneously — with a narrow, specific exception for uncorrelated subqueries used in a DELETE/UPDATE WHERE clause.

  1. An UPDATE/DELETE whose WHERE clause subquery references the same table being modified, in a way that's correlated (depends on the outer row) rather than the narrower permitted uncorrelated form.
  2. An INSERT ... SELECT where the SELECT reads from the very table being inserted into.
  3. A well-intentioned attempt to "clean up duplicates" or "sync a table against itself" using a self-referencing subquery, without realizing this specific pattern is disallowed.
Solutions / Resolution
  1. Load the relevant input data into a temporary table first, per the official guidance, then run a separate DELETE/UPDATE/INSERT against the original table referencing that temp table instead of the original table itself.
  2. Check whether the subquery can be rewritten as an uncorrelated one in a DELETE/UPDATE WHERE clause, if that narrower exception actually covers the intended logic.
  3. Break the operation into two clearly separated statements — one that reads and stages data, and a second that modifies the target table based on the staged data.
Examples
The disallowed self-referencing pattern
DELETE FROM orders
WHERE order_id IN (
    SELECT MIN(order_id) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1
);
-- -360 in some forms of this pattern, depending on correlation

Fix — stage the input into a temp table first:

SELECT MIN(order_id) AS order_id
INTO TEMP dup_orders
FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM dup_orders);
Diagnostic Checks
  1. Check whether the statement's subquery references the same table being modified.
  2. Determine whether the subquery is correlated or uncorrelated — only a narrow uncorrelated case is permitted directly.
Related Errors / Related Topics
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

Stage the subquery's result into a temporary table first, then run the modification against the original table referencing that temp table — this sidesteps the self-reference restriction entirely.