Re: Multiple-Row Update in one Statement

From: Michael Friedman <mfriedma_at_us.oracle.com>
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:

>Hello Beloved Oracle Gurus:
 

>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

Original text of this message