[1]: (Error): ORA-01427: single-row subquery returns more than one row 1

[jj14]

I am trying to update about 475,000 rows in the database, but I am getting the following error. I also tried using a cusor, same result. In some cases below, franno is equal to sitefran:

[1]: (Error): ORA-01427: single-row subquery returns more than one row

update tandem_july_nw
set total = (select amtgal * price * .96
from tandem_july_nw
where franno <> sitefran);


Thanks, I would appreciate your quick responce.

 

[BJCooperIT]

In order for your update to work properly:

(select amtgal * price * .96
   from tandem_july_nw
  where franno <> sitefran)

must return a single value to set the value of TOTAL in TANDEM_JULY_NW.

Aliases might help highlight the problem:

update tandem_july_nw A
set A.total =  (select B.amtgal * B.price * .96
                from   tandem_july_nw B
                where  B.franno <> B.sitefran);

Is that what you intended? Assuming there are multiple rows in TANDEM_JULY_NW, without a WHERE clause matching columns in A to B, the SQL will update all rows.


[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[jj14]

Thanks, that seems to be working properly. Also I modified it to exclude the select statement and that worked on a test, is this correct? The reason why I did not use an alias is because all the columns are in the same table and the columns are distinct:

update tandem_july_nw
set total = (amtgal * price * .96)

where franno <> sitefran;

 

[BJCooperIT]

Your revised SQL will update each qualifying row in TANDEM_JULY_NW with that row's calculated total.

Sample Original Data

FRANNO   SITEFRAN  AMTGAL   PRICE   TOTAL
123      123       5.5      6
564      564       10       9
456      867       7        2.3
376      276       8        4

Sample Updated Data

FRANNO   SITEFRAN  AMTGAL   PRICE   TOTAL
123      123       5.5      6
564      564       10       9
456      867       7        2.3     15.456
376      276       8        4       30.72

The first two rows are not updated because FRANNO=SITEFRAN.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[jj14]

That is exactly correct. Thanks so much.