RE: SQL - "Result of SELECT more than one row."

You need to make sure that the result set includes only one row from
each file. If you have multiple records in the secondary file, which
one updates your primary file?



Chris Bipes
Director of Information Services
CrossCheck, Inc.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Wells
Sent: Friday, September 04, 2009 12:42 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL - "Result of SELECT more than one row."

OK, I trying write what I think should be a simple SQL update statement,
but
keep getting "Result of SELECT more than one row.". I have searched
this
forum and tried the suggestions found, but obviously I am still missing
something. I guess this is what happens on Friday afternoon after a
long
week!

Any way, I want to update a field in file A when other fields in file A
match fields in file B.

The following select statement produces a list of records that I want to
update:

select a.stm_crltr
from idxstmwrk1 a, crltrwrk1 b
where a.stm_grp =b.crltr_grp and
a.stm_mrn =b.crltr_mrn

I thought this would work -

update idxstmwrk1
set stm_crltr = (select 'Y'
from idxstmwrk1 a, crltrwrk1 b
where a.stm_grp=b.crltr_grp and
a.stm_mrn=b.crltr_mrn)

and then I tried this -

update idxstmwrk1
set stm_crltr = (select 'Y'
from idxstmwrk1 a, crltrwrk1 b
where a.stm_grp=b.crltr_grp and
a.stm_mrn=b.crltr_mrn)
where exists (select 'Y'
from idxstmwrk1 a, crltrwrk1 b
where a.stm_grp=b.crltr_grp and
a.stm_mrn=b.crltr_mrn)

However, both resulted in the "Result of SELECT more than one row."
error!