Delete with "Join" in Oracle SQL Query

Delete with Join in Oracle sql Query

Based on the answer I linked to in my comment above, this should work:

delete from
(
select pf.* From PRODUCTFILTERS pf 
where pf.id>=200 
And pf.rowid in 
  (
     Select rowid from PRODUCTFILTERS 
     inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
     And PRODUCTS.NAME= 'Mark'
  )
); 

or

delete from PRODUCTFILTERS where rowid in
(
select pf.rowid From PRODUCTFILTERS pf 
where pf.id>=200 
And pf.rowid in 
  (
     Select PRODUCTFILTERS.rowid from PRODUCTFILTERS 
     inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
     And PRODUCTS.NAME= 'Mark'
  )
); 

How to delete a record using inner join on Oracle SQL?

Only one From table is allowed when performing a Delete.
try

DELETE FROM A_B 
WHERE EXISTS (SELECT 1 FROM A 
  INNER JOIN B ON (B.ID = A_B.B_ID) 
  WHERE A.ID = A_B.A_ID
  AND B.NAME IN ('X', 'Y') 
  AND A.NAME = 'Z');

reference here

Deleting from Oracle SQL table using 'inner join'

The EXISTS version would look like this:

delete from table2
where exists (select *
              from table1
              where table1.column1 = table2.column2);

Alternatively you can use an IN clause

delete from table2
where column2 in (select column1
                  from table1);

ORACLE SQL deleting the results of a query with a join

Using:

delete app_access 
where empid IN (SELECT B.EMPID FROM PERSL B WHERE B.CAD_CMD_MASK=7864320)

How to delete from table with join

In Oracle, you would use a correlated subquery.

delete from painting p
where exists (
    select 1 from museum m where pm.museum_name = m.museum_name and m.mu_country = 'France'
)

Delete Records from table on the basis of inner join

Delete where exists

delete
from collection as c
where exists (
  select 1
  from collection_h as h
  where h.pos_protocol_id  = c.pos_protocol_id
    and h.terminal_pos_number = c.terminal_pos_number 
    and h.cb_file_number = c.cb_file_number 
    and h.cb_block_number = c.cb_block_number 
    and h.is_stan_batch = c.is_stan_batch  
    and h.is_transaction_date = c.is_transaction_date
    and h.is_stan_trans = c.is_stan_trans
);

Simplified test on db<>fiddle here

But if the columns can have NULL's in the matching rows

delete
from collection as c
where exists (
  select 1
  from collection_h as h
  where decode(h.pos_protocol_id, c.pos_protocol_id, 0, 1) = 0
    and decode(h.terminal_pos_number, c.terminal_pos_number, 0, 1) = 0 
    and decode(h.cb_file_number, c.cb_file_number, 0, 1) = 0
    and decode(h.cb_block_number, c.cb_block_number, 0, 1) = 0
    and decode(h.is_stan_batch, c.is_stan_batch, 0, 1) = 0
    and decode(h.is_transaction_date, c.is_transaction_date, 0, 1) = 0
    and decode(h.is_stan_trans, c.is_stan_trans, 0, 1) = 0
);

---

Related Topics