Re: A subquery returning two columns

On 27 Jun 2003 21:41:22 -0700, vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote:

>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.

select ...
from ...
where (<column1>,<column2>) in (select <column1>,<column2>)

                                                         from...
                                                        )

Standard SQL ;-)

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jun 28 2003 - 01:45:37 CDT