Tuan Tuan - 3 months ago 13
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:

A(type1, 192.168.94.1)
A(type1, 192.168.94.2)
A(type2, 192.168.94.1)

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, 192.168.94.1)

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

In table A, I want delete the row

A(type1, 192.168.94.1)


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
-> 192.168.94.1 = 192.168.94.1


Could you give me some advice?

Answer
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)
    )