OraFAQ Forum: SQL & PL/SQL » a subquery returned not exactly one row

Home » SQL & PL/SQL » SQL & PL/SQL » a subquery returned not exactly one row

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

a subquery returned not exactly one row [message #23106]

Mon, 18 November 2002 13:44

Jay
Messages: 127
Registered: October 1999

Senior Member

Hi

I am trying to update a table in UNIX using Informix-SQL. I get an error message that state a subquery returned not exactly one row. I tried using a declare/open/fetch query, but I don't think that Informix-SQL allows this. My code is simple and is as follows:

update act
set co_no = (select county_no from spk_city
where spk_city.zip = act.zip)
where act.zip in
(select zip from spk_city)

I think the problem is becuase there are duplicate zip codes in the act table. Am I correct?

Report message to a moderator

Re: a subquery returned not exactly one row [message #23107 is a reply to message #23106]

Mon, 18 November 2002 13:49

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Yes. To identify the duplicate(s):

select zip, count(*)
  from spk_city
 group by zip
 having count(*) > 1;

Report message to a moderator

Re: a subquery returned not exactly one row [message #23127 is a reply to message #23106]

Tue, 19 November 2002 09:30

Alex Mazur
Messages: 17
Registered: March 2002

Junior Member

update act
set co_no = (select distinct county_no from spk_city
where spk_city.zip = act.zip)
where act.zip in
(select zip from spk_city)

Report message to a moderator

Re: a subquery returned not exactly one row [message #23130 is a reply to message #23127]

Tue, 19 November 2002 10:17

Jon
Messages: 483
Registered: May 2001

Senior Member

I believe this would only work if both zip and county_no are duplicates; if the zip is duplicate but the county_no is not, you will have the same problem. I haven't tested that, but I believe that to be the case.

Report message to a moderator

Previous Topic:	Table Stats
Next Topic:	Tabular view of normalized table?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 03:03:18 CDT 2024