update from another table row by row

From: David Zlotchenko 1 <zlotchen_at_voyager.rtd.utk.edu>
Date: 5 Nov 1999 16:19:20 GMT
Message-ID: <7vv028$5c7$1@penn.dii.utk.edu>

I have table t1:
id char(8) primary key,
status varchar2(100),
name varchar2(200)
);

and table t2:
id char(8),
category varchar2(10),
data varchar2(200)
);

Table t2 contains data for fields status and name in the table t1.

I can view my data like this:
select A.id,A.data,B.data from

        (select C.id,D.data from t1 "C", t2 "D"
        where D.id=D.id and D.category='status') "A",
        (select data from t1 "E", t2 "F"
        where E.id=F.id and F.category='approver') "B"

;

How do I put the image of the above dynamic table into the table t1?

When I try to put it into update clause, it gives me an error ORA-01427: single-row subquery returns more than one row.

Do I have to use cursor only? Can I use a view to update multiple rows
conditionally?

Thanks for your help. DAvid.

--
--
David Zlotchenko
E-mail: zlotchen_at_cs.utk.edu
WWW: http://www.oars.utk.edu/~zlotchen Received on Fri Nov 05 1999 - 10:19:20 CST