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

Perfect! Thank you very much. I have a lot to learn about sql. Heck, a
few years ago I could not even spell it!

Have a great weekend!

Joe

Joe Wells
University of Alabama Health Services Foundation
500 22nd Street South, Suite 308
Birmingham, AL 35233
205-731-5610

CONFIDENTIALITY NOTICE
This e-mail is intended for the sole use of the individual(s) to whom it is
addressed, and may contain information that is privileged, confidential and
exempt from disclosure under applicable law. You are hereby notified that
any dissemination, duplication or distribution of this transmission by
someone other than the intended addressee or its designated agent is
strictly prohibited. If you receive this e-mail in error, please notify me
immediately by replying to this e-mail.

On 9/4/2009 at 3:11 PM, in message

<OF82072A7D.F31139A6-ON85257627.006E0D2C-85257627.006EE2EF@xxxxxxxx>, Lloyd
Bailey<lbailey@xxxxxxxx> wrote:

I think your looking to do the following. I did not test this, but it
should be enough to demonstrate what you need.

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

"Result of SELECT more than one row." is basically stating your inner
select for the set argument is returning back multiple rows. It cant map
multiple values to one variable. This inner select needs to return only
one
value for the set argument.

Lloyd Bailey
Office: (631) 244-2165
Cell: (631) 445-5344
Fax: (631) 200-6113



"Joe Wells"
<jwells@xxxxxxxxx

>
To
Sent by:
midrange-l@xxxxxxxxxxxx
midrange-l-bounce

cc

s@xxxxxxxxxxxx

Subject
SQL - "Result of SELECT more than
09/04/2009 03:55 one row."
PM


Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






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




Joe Wells
University of Alabama Health Services Foundation
500 22nd Street South, Suite 308
Birmingham, AL 35233
205-731-5610

CONFIDENTIALITY NOTICE
This e-mail is intended for the sole use of the individual(s) to whom it
is
addressed, and may contain information that is privileged, confidential
and
exempt from disclosure under applicable law. You are hereby notified
that
any dissemination, duplication or distribution of this transmission by
someone other than the intended addressee or its designated agent is
strictly prohibited. If you receive this e-mail in error, please notify
me
immediately by replying to this e-mail.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.