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

Run this query:

select crltr_grp, crltr_mrn, count(*) as duplicates
from crltrwrk1
group by crltr_grp, crltr_mrn
having count(*) > 1
order by 3 desc

If you get any hits in that query, you have a one-to-many relationship
between the two files and will need to pick one of those duplicate rows for
an update (you can't update one row with multiple rows).
This 'row picking' can be done in a number of ways, but before we start
examining that, let's see if you have duplicates in your 'from' file or not.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
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!

What am I doing wrong?

Thanks,

Joe