How to delete values from only one table with join and where clause from other table

Question

I need to remove data from one table but use a different table for the where clause. So I only want to remove the values I get in the output and not anything in a different table.

The table I want to remove data from has 2 rows: ot_id and classification_id

select 
t0.*
from ot_classification t0
left join
ot t1
on t0.ot_id=t1.id
where t1.ct = '22GC'
and t0.classification_id not in '157101'

what I think I should do:

delete t0.ot,
t0.classification_id
from ot_classification t0
left join
ot t1
on t0.ot_id=t1.id
where t1.ct = '22GC'
and t0.classification_id not in '157101'

I'm afraid this will also delete the t1.id and that's not what I want. how do I avoid that?

Answer 1

Your delete syntax is not supported in Oracle.

I would recommend exists:

delete from ot_classification t0
where 
    t0.classification_id != 157101
    and exists (
        select 1
        from ot t1
        where  t0.ot_id = t1.id and t1.ct = '22GC'
)

In Oracle 23c, we can also phrase the query like so:

delete ot_classification t0
from ot t1 
where 
    T0.ot_id = t1.id 
    and t1.ct = '22GC' 
    and t0.classification_id != 157101