Tuan Tuan - 2 months ago 6x
SQL Question

Deleting a table in Oracle with WHERE condition in other tables

I have two tables, A and B, in Oracle:

A (a1, a2)

B (b1, b2, b3, b4, b5)

And some rows like:


B(type1, 192, 168, 94, 1)
B(type1, 192, 168, 94, 3)
B(type2, 192, 168, 94, 2)

In table A, we have A(type1,

In table B, we also have B(type1, 192, 168, 94, 1)

In table A, I want delete the row


Because its type like the type in table B

A.a1 = B.b1
-> type1 = type1

and the ip address duplicate when concatenating in B

A.a2 = B.b2 ||'.'|| B.b3 ||'.'|| B.b4 ||'.'|| B.5
-> =

Could you give me some advice?

delete from A
where ROWID in 
    (select A.ROWID
     from A,B
     where A.A1 = B.B1
     and A.A2 = concat(B.B2, B.B3, B.B4)