Re: Multiple-Row Update in one Statement
Date: Thu, 18 Feb 1993 18:49:22 GMT
Message-ID: <1993Feb18.184922.18886_at_oracle.us.oracle.com>
In article <1993Feb17.183048.5646_at_cbfsb.cb.att.com> gerette_at_cbnewsb.cb.att.com (marianne.g.pittorino) writes:
>Is there any way in Pro*C to update more than one row with more than one value
>in one statement? For example, let's pretend I have two tables, TABLE1 and
>TABLE2.
> TABLE1 looks like: TABLE2 looks like:
> ----------------- -----------------
> ID number(5), ID number(5),
> TAG char(10) TAG char(10),
>The data in each table:
> TABLE1 TABLE2
> ------ ------
> 1 <no tag yet> 1 first Y
> 2 <no tag yet> 2 second N
> 3 third Y
>I want to set the TAG column in TABLE1 to the value in the TAG column from
>TABLE2 for the corresponding ID values. I tried this:
> update TABLE1 set tag =
> (select t2.tag from TABLE1 t1, TABLE2 t2 where t1.id = t2.id)
> where id in (
> select t1.id from TABLE1 t1, TABLE2 t2 where t1.id = t2.id);
>but I get "ORA-01427: single-row subquery returns more than one row" on the
>"(select t2.tag from TABLE1 t1, TABLE2 t2 where t1.id = t2.id)" line.
The problem is that you are trying to set tag equal to the result of the first subquery, but that subquery returns multiple records. That's a no-no.
Try
update TABLE1 t1 set tag = (select t2.tag from TABLE2 t2 where t1.id = t2.id) where id in ( select t2.id from TABLE2 t2 );
Mike Received on Thu Feb 18 1993 - 19:49:22 CET