Re: Problem with UPDATE

From: Tommy Wareing <p0070621_at_oxford-brookes.ac.uk>
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

Original text of this message