Error ORA-01427 When Run Update

[zedd]

Good afternoon / day / night,

I'm inikiting my life in the Oracle world and I'm going through a boring problem, but I hope they can help me.

I am developing a script where there is a column in a table that I can not do the update.

In this column, the Sumator of items sold by all sellers of a particular item should be stored, but when I execute the error

Select all

ORA-01427: single-row subquery returnsmore then une row

SELECT pure wheel right, but when using inside the stick update. I already researched several sites, I took the structure and other tests but did not give anything. How can I do so can do this update in the field?

follow the code:

Select all

UPDATE vendas SET unidades =( 
SELECT 
   NVL(vend1,0)+NVL(vend2,0)+NVL(vend3,0)+NVL(vend4,0)+NVL(vend5,0)+NVL(vend6,0)+NVL(vend7,0)+NVL(vend8,0) 
       FROM 
              vendas);

is missing insert or withdraw something? ::?:::?:

Thanks there for the attention! : Wink:

[victorhugomuniz]

Error:
ORA-01427: Single-Row Subquery Returns More than One Row
Cause:
You Tried to execute an SQL Statement that contained the Subquery That Returns More than One Row.
Action:
The options to solve this oracle error are:

1. Rewrite Your Query So That The Subquery Only Returns One Row.
2. Change Your Query to Use One of the Following Following Against Your Subquery Results:
* any
* ALL
* In
* Not In


For example, if you tried to run the following SQL Statement:

Select all

    select * from orders 
    where supplier_id = 	(select supplier_id 
    	 from suppliers 
    	 where supplier_name = 'IBM');

And There Was More Than One Record In The Suppliers Table With The Supplier_Name Of IBM, You Would Receive The Following Message:

[IMG http://www.techonthenet.com/oracle/erro ... 27_001.png[/img]
The Most Common Way to Correct This SQL Statement is to use the in function the follows:

Select all

    select * from orders 
    where supplier_id IN 	(select supplier_id 
    	 from suppliers 
    	 where supplier_name = 'IBM');

[url=http://www.techonthenet.com/oracle/errors/ora01427.php]fonte

[victorhugomuniz]

PL / SQL: How to Update Using Select

This "Problem" CAME ACRROS ME WHEN I Needed to Update An entire Temporary Table Column. One OPTION IS TO LOOP ALL TABLE ROWS, BORING AND TIME Consumer Task. After adding Background Searchs I'vê Adopted this Method:



I'vê Basically One Inner Select and Join The Two Tables In The Inner Select's Where Clause .
[/quote]

[gfkauer]

This is happening that your sales table has more than one record and has no WHERE in the subquery.

To solve you must rewrite so that SubQuery only returns a record.

If the expected result is a change in all rows of the table sales you can do so:

Select all

 
UPDATE vendas v1 SET unidades =( 
SELECT 
NVL(vend1,0)+NVL(vend2,0)+NVL(vend3,0)+NVL(vend4,0)+NVL(vend5,0)+NVL(vend6,0)+NVL(vend7,0)+NVL(vend8,0) 
FROM 
vendas v2 where v2.PK = v1.PK); 

where the PK fields that I put would be the columns of your PK.

[zedd]

Thanks for everyone who posted but analyzing more calmly I detected where the problem was, faced with the result we expected, and then I saw what I was doing wrong.

In fact I did not need to make a subquery, since I wanted the sum of columns that contained values, it was only necessary to make a common update.

I traveled legal and passed over the solution without necessity. Follows corresponding

Select all

 
update vendatsamb SET und = ( 
             NVL(vend1,0) 
             +NVL(vend2,0) 
             +NVL(vend3,0) 
             +NVL(vend4,0) 
             +NVL(vend5,0) 
             +NVL(vend6,0) 
             +NVL(vend7,0) 
             +NVL(vend8,0)) 

Simple thus: -O

thanks for the cooperation of all: D