Re: Multiple-Row Update in one Statement

gerette_at_cbnewsb.cb.att.com (marianne.g.pittorino) writes:
|> 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
|>
|>
|> 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.

Assuming the ID columns are unique for both tables, the query could be written as follows:

update TABLE1 t1

   set tag = (select t2.tag from TABLE2 t2 where t1.id = t2.id)    where exists

        (select t2.id from TABLE2 t2 where t1.id = t2.id);

[The where exists clause may even be thrown out altogether if the ID's  in TABLE1 is a subset of the ID's in TABLE2]

If TABLE2 does contain duplicate ID's then you may have to substitute min(t2.tag) or max(t2.tag) for t2.tag in the first subquery.

Hope this works for you.

Roderick Manalac
Oracle Corporation Received on Thu Feb 18 1993 - 05:28:21 CET