community.borland.com

Article #25531: Multiple rows in singleton select occurs when attempting an update

Problem:
I am getting this error when I try to run an update statement:  
 "Multiple rows found in singleton select."


Solution:
This error happens when you try to put in multiple values where the system 
expects to only see one.    When you do an update statement, the select
statement that comes after the word 'set' is expected to result in one value.
If the select statement you are using results in more than one value,
you will get this error.

eg.  Here is an example of how this error can come about.  If you have
a table called breedlist that looks like the following:

PUPPY                       BREED              FATHER_NAME 
========== ================  =========== 
Penelope                     poodle               Rover       
Peter                             poodle               Rover       
Paula                            poodle               Rover 
Rufus                            beagle               Sport    
Roscoe                         beagle               Sport  

and you want to put breeds into the dogs table from the breed_list table, 
you might do a query like the following to accomplish this result:

 update dogs 
                    set breed = (select breed from breedlist where father_name='Rover')


will generate this error if there is more than one record that meets the criteria of the
select.  On the other hand the query below will not generate this error.  

update dogs
         set breed = (select distinct breed from breedlist where father_name='Rover')

The update query listed above will work because the select statement will return only
one value.  If you ran the select listed above , you would get this result.

BREED
=======
poodle


Last Modified: 26-OCT-00