PL/SQL error: ORA-01427: single-row subquery returns more than one row

Here is the code:

UPDATE sfbetrm
SET sfbetrm_ar_ind =
(SELECT fa_info.ok
FROM fa_info
WHERE sfbetrm_ar_ind NOT IN ('C', 'Y')
AND fa_info.ok = 'Y'
AND sfbetrm_pidm = fa_info.pidm)
WHERE '&&update' = 'Y' AND sfbetrm_term_code = '201620'
AND EXISTS
(SELECT 1
FROM fa_info
WHERE sfbetrm_ar_ind NOT IN ('C', 'Y')
AND fa_info.ok = 'Y'
AND sfbetrm_pidm = fa_info.pidm);

How can this be fixed?  Oddly, it had been working in the past.  We did update the Oracle level since it was last run.
The error is PL/SQL error: ORA-01427: single-row subquery returns more than one row.
Jack
Jack SeamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jack SeamanAuthor Commented:
I thinking this modification would work.

UPDATE sfbetrm
SET sfbetrm_ar_ind =
(SELECT fa_info.ok
FROM fa_info, sfbetrm
WHERE sfbetrm_ar_ind NOT IN ('C', 'Y')
AND fa_info.ok = 'Y'
AND sfbetrm_pidm = fa_info.pidm
AND sfbetrm_term_code = '201620')


Any thoughts?
johnsoneSenior Oracle DBACommented:
This is a data issue and would have nothing to do with an Oracle upgrade.

Run the sub-query.  It will return more than one record.  Only you can determine which record is correct.  There is something missing from your where clause that is causing the sub-query to return multiple records.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jack SeamanAuthor Commented:
I rewrote the query and posted it eariler.  I tested it out and it seems to work.  The orginal query seems overly complicated.  Any thoughts?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

johnsoneSenior Oracle DBACommented:
Rewritten query is not the same as the original query.  Rewritten query has no where clause and would therefore update every row in the table.  I don't think that is the intention.
Jim HornSQL Server Data DudeCommented:
Just for kicks and giggles, highlight the last subqueries and hit execute, then highlight from the third line on down and execute, see how many rows are returned.   If more than one row, there's the error, as you're setting the subquery return set to a single column value.

If this is a data issue it's going to limit our ability to help, as experts here cannot connect to your data source and run queries.

btw Missing a final ); probably just a copy-paste issue.
johnsoneSenior Oracle DBACommented:
I don't see an unmatched parenthesis.  Not sure where you see it.  Look at a better formatted version of the query:
UPDATE sfbetrm 
SET    sfbetrm_ar_ind = (SELECT fa_info.ok 
                         FROM   fa_info 
                         WHERE  sfbetrm_ar_ind NOT IN ( 'C', 'Y' ) 
                                AND fa_info.ok = 'Y' 
                                AND sfbetrm_pidm = fa_info.pidm) 
WHERE  '&&update' = 'Y' 
       AND sfbetrm_term_code = '201620' 
       AND EXISTS (SELECT 1 
                   FROM   fa_info 
                   WHERE  sfbetrm_ar_ind NOT IN ( 'C', 'Y' ) 
                          AND fa_info.ok = 'Y' 
                          AND sfbetrm_pidm = fa_info.pidm); 

Open in new window

Jim HornSQL Server Data DudeCommented:
You are correct, I didn't see the right paren in this line -->  AND sfbetrm_pidm = fa_info.pidm).

So my first comment should read 'highlight both subqueries sqparately and execute'.
Jack SeamanAuthor Commented:
The rewriten code does have a where clause.  I'm missing something?
johnsoneSenior Oracle DBACommented:
The rewritten query has a where clause in the sub-query.  The update doesn't have a where clause.  This would cause all rows in the table to be updated as there is no restriction on the update statement.
Jack SeamanAuthor Commented:
I've discovered what I think is the problem.  The script that creates the FA_info table inserts multiple records.  I looked as data from previous runs from a diferent sememter (term code) and there are no duplicates in the table.  There must be something wrong with the "unique index" too.
Here is the table creation and load script for the users.  Any thoughts?
DROP TABLE fa_info CASCADE CONSTRAINTS;
CREATE TABLE fa_info
(
pidm NUMBER (8),
ban_id VARCHAR2(36),
lname VARCHAR2(240),
fname VARCHAR2(240),
term VARCHAR2 (6),
disburse NUMBER (7, 2),
memo NUMBER (7, 2),
loan NUMBER (7, 2),
offer NUMBER (7, 2),
award NUMBER (7, 2),
ttuition NUMBER (7, 2),
dtuition NUMBER (7, 2),
scharge NUMBER (7, 2),
fees NUMBER (7, 2),
Housing NUMBER (7, 2),
Meals NUMBER (7, 2),
charge NUMBER (7, 2),
later NUMBER (7, 2),
bill NUMBER (7, 2),
cash NUMBER (7, 2),
diff NUMBER (7, 2),
track VARCHAR2 (6),
budget VARCHAR2 (6),
pack VARCHAR2 (6),
p_comp DATE,
preq_date DATE,
ver VARCHAR2 (1),
ver_comp VARCHAR2 (1),
progress VARCHAR2 (1),
isir VARCHAR2 (1),
rstate VARCHAR2 (1),
cert VARCHAR2 (1),
sap VARCHAR2 (6),
hold VARCHAR2 (1),
efc NUMBER (6),
major VARCHAR2 (6),
resident VARCHAR2 (1),
hours NUMBER (4, 2),
FA_HOURS NUMBER (4, 2),
attempt NUMBER (11, 3),
earn NUMBER (11, 3),
GPA NUMBER (4, 3),
ok VARCHAR2 (1),
part_of_term VARCHAR2 (1)
)
TABLESPACE users;
 
CREATE UNIQUE INDEX pk_fa_info
ON fa_info (pidm,term, part_of_term);
REM *************************************************** Student
INSERT INTO fa_info (pidm, ban_id, lname, fname, term, part_of_term)
SELECT DISTINCT
sfrstcr_pidm pidm,
spriden_id ban_id,
spriden_last_name lname,
spriden_first_name fname,
sfrstcr_term_code term,
CASE (ssbsect_ptrm_code) WHEN 'W' THEN ssbsect_ptrm_code ELSE NULL END
part_of_term
FROM sfrstcr, ssbsect,spriden
WHERE sfrstcr_term_code = '201620'
AND sfrstcr_rsts_code IS NOT NULL
AND sfrstcr_crn = ssbsect_crn
AND sfrstcr_pidm = spriden_pidm
AND spriden_change_ind IS NULL
AND sfrstcr_term_code = ssbsect_term_code;
COMMIT;
Jack SeamanAuthor Commented:
I've discovered why there are duplicates.  We have a 'winter' term that students pay for thru financial aid of the Spring.  So there is no need for seperate records for winter students.  When I pull out these records from the table, the other script should run fine.
johnsoneSenior Oracle DBACommented:
As a point of terminology...  There really isn't a unique index.  There is an index an a unique constraint.  You are creating the two at the same time with that statement you have.  Oracle has recommended (since somewhere around 9) that you separate the two like this:

create index pk_fa_info_idx
on fa_info (pidm,term, part_of_term);

alter table fa_info
add constraint pk_fa_info
unique (pidm,term, part_of_term);

This has the advantage of being able to disable and enable the constraint without having to rebuild the underlying index.

Also, if you had an index with more columns in it (for example pidm, term, part_of_term, ban_id), then this index could be used to enforce the unique constraint without having to duplicate the storage of an index.
Jack SeamanAuthor Commented:
Both experts gave me good insight to track down the cause of my error.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.