Oninit Logo
The Down System Specialists
+1-913-674-0360
+44-2081-337529
Partnerships Contact
Finderr

-360 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);