Multiple-Row Update in one Statement
From: marianne.g.pittorino <gerette_at_cbnewsb.cb.att.com>
Date: Wed, 17 Feb 1993 18:30:48 GMT
Message-ID: <1993Feb17.183048.5646_at_cbfsb.cb.att.com>
Date: Wed, 17 Feb 1993 18:30:48 GMT
Message-ID: <1993Feb17.183048.5646_at_cbfsb.cb.att.com>
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), ACT_IND char(1) The data in each table: TABLE1 TABLE2 ------ ------ 1 <no tag yet> 1 first Y 2 <no tag yet> 2 second N 3 third Y
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.
Please say that the obvious eludes me and show me the way!
Thanks,
Marianne
Received on Wed Feb 17 1993 - 19:30:48 CET