DELETE FROM <subquery>

Question

DELETE FROM (SELECT * FROM orders1 o,order_item1 i
WHERE i.order_id = o.order_id );

above query has not mentioned particular to delete rows.Instead of i have mentioned a sub query. But this query executes without any error and deletes order_item1 table rows.

How oracle server decide which table rows should be deleted? why orders1 table rows cannot be deleted?

Answer 1

In your case, the subquery's table orders1 has most certainly a primary key (order_id ) and the table order_item1 has the corresponding foreign key order_id.

Thus, Oracle turns the table order_item1 into a key preserved table and is able to delete from this table in the outer delete statement.

The concept of key preserved tables is further explained at This tahiti documentation link.

If you want to find out what is deleted you can go like so

These are the tables that are joined in your subquery:

create table tq84_orders1 (
   id     number primary key,
   col_1  varchar2(10),
   col_2  date
);

create table tq84_order_item1 (
  order_id references tq84_orders1,
  col_3    varchar2(10),
  col_4    date
);

This view emulates the subquery:

create view tq84_orders_v as (
  select * 
  from
    tq84_orders1     o,
    tq84_order_item1 i
  where
    o.id = i.order_id
);

This query (on user_updateable_columns) now finds which columns actually get deleted (or can be deleted):

select
  table_name,
  column_name,
--updatable,
--insertable,
  deletable
from
  user_updatable_columns
where
  table_name = 'TQ84_ORDERS_V';

The result shows that in effect the three columns ORDER_ID, COL_3 and COL_4 can be deleted, all of which stem from TQ84_ORDER_ITEM1.

TABLE_NAME                     COLUMN_NAME                    DEL
------------------------------ ------------------------------ ---
TQ84_ORDERS_V                  ID                             NO
TQ84_ORDERS_V                  COL_1                          NO
TQ84_ORDERS_V                  COL_2                          NO
TQ84_ORDERS_V                  ORDER_ID                       YES
TQ84_ORDERS_V                  COL_3                          YES
TQ84_ORDERS_V                  COL_4                          YES