CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
TNK
TNK

Reputation: 305

DELETE FROM <subquery>

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?

Upvotes: 7

Views: 16614

Answers (5)

René Nyffenegger
René Nyffenegger

Reputation: 40489

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

Upvotes: 12

Jack
Jack

Reputation: 347

This might help you

DELETE FROM orders1 o CROSS JOIN order_item1 i WHERE i.order_id = o.order_id

Upvotes: -2

Hamidreza
Hamidreza

Reputation: 3118

I think that if you want to Delete rows from just ONE table you can use this query:

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

But if you want to delete rows from both tables you can do something like this:

CREATE TABLE TEMP_TAB AS SELECT o.order_id FROM orders1 o,order_item1 i
WHERE i.order_id = o.order_id;

DELETE FROM order_item1 WHERE order_id in 
(SELECT TEMP_TAB.order_id FROM TEMP_TAB);

DELETE FROM orders1 WHERE order_id in 
(SELECT TEMP_TAB.order_id FROM TEMP_TAB);

DROP TABLE TEMP_TAB;

Upvotes: 2

mrrodd
mrrodd

Reputation: 126

I don't have access to an Oracle environment at the moment so I can't verify, but the following does work in SQL Server and will delete from the Orders table. If you want to delete from Order_Item, reverse the tables.

DELETE o
FROM   Orders o
       JOIN Order_Item oi ON o.order_id = oi.order_id
WHERE  [filter condition]

Upvotes: 0

Rajesh Kanna
Rajesh Kanna

Reputation: 1

in your Sub Query you gave like "i.order_id = o.order_id".you can try like this

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

this will be correct

Upvotes: 0

Related Questions