Re: Problem with UPDATE
Date: 21 Apr 1994 03:41:53 -0500
Message-ID: <CoLpBz.Cpx_at_uk.ac.brookes>
B C Zygmunt (bzy_at_ornl.gov) wrote:
> 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.
I'm guessing here, but I think that when your subquery tests
table_name=x.table_name
the first use of table_name (the one with no table specified) is being
checked against old_data_dict. Since for every row in that table,
old_data_dict.table_name=x.table_name
the subquery is returning every row from old_data_dict. (*)
The same goes for column_name as well.
You could try:
SQL> update data_dict y set units =
2 (select units from old_data_dict x where
3 y.table_name = x.table_name and
4 y.column_name = x.column_name);
Of course I could be completely wrong :)
(*) Every row where table_name is not null, that is.
-- _________________________ ________________________________ / Tommy Wareing \ / Dying ain't much of a living, \ | p0070621_at_brookes.ac.uk X When you're out and on the run,| \ 0865-483389 / \ Dying ain't much of a living, | ~~~~~~~~~~~~~~~~~~~~~~~~~ \ When you're young / ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Received on Thu Apr 21 1994 - 10:41:53 CEST