Re: Problem with UPDATE

bzy_at_ornl.gov writes in article <1994Apr20.194132.22568_at_ornl.gov>:
>
> This will probably turn out to be something really stupid and I'll be terribly
> embarrassed about having asked it, but here goes . . .
>
> I'm having problems with the UPDATE command. This is what I'm getting:
>
> SQL> update data_dict set units =
> 2 (select units from old_data_dict x where
> 3 table_name = x.table_name and
> 4 column_name = x.column_name);
> (select units from old_data_dict x where
> *
> ERROR at line 2:
> ORA-01427: single-row subquery returns more than one row
>
> I know that there is only a single row in table "x" with the same table_name
> and column_name as each row in the original table. I also know that I can
> rewrite the code in PL/SQL and get it to work, but I'd prefer to find out what
> I'm doing wrong here.
>

You have to join the table which you want to update with the table you are doing the query...

update tab1
  set field =

        (select value from tab2
            where tab1.key = tab2.key)

Willy Klotz

Willys Mail     FidoNet   2:2474/117  2:2474/118     Mail Only System
                CIS: 100020,3517       USR Courier HST dual standard
                willyk_at_kbigate.stgt.sub.org
                ->   No Request from 06.00 to 08.00 <-
======================================================================