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.
midrange.com
