A subquery returning two columns

Fo any version of Oracle

How do I base a query (or DELETE, UPDATE statements) on a subquery if it returns two columns (other than concatenating these two columns in one)?

For example, let's say we have table TABLE1 with columns COL1, COL2, etc

My subquery looks like that

SELECT COL1, COL2
FROM TABLE1
GROUP BY COL1, COL2
HAVING ... (I need HAVING, not WHERE).

Now, if the subquery were returning only one column I could have written

SELECT * FROM TABLE2 T2
  WHERE T2. COL3 IN
   (SELECT COL1 FROM TABLE1 ...) or

DELETE FROM TABLE2 T2 WHERE T2.COL3 IN (subquery).

But what if I need two columns?

At least if all I need is a query I can create a view based on the subquery and then join with this view

SELECT T2.COL1, T2,COL2, T2.COL3, T2.COL4 FROM TABLE2 T2, MYVIEW V
WHERE T2.COL3=V.COL1 and T2.COL4=V.COL2.

But what if I need to write DELETE statement? I can't use a join in DELETE, only a subquery.

Thanks

P.S. I checked the manual (SQL Reference) once again: for UPDATE statement it talks about CORRELATED subqueries, but the purpose is to update different rows to different values. Received on Fri Jun 27 2003 - 23:41:22 CDT