Error ORA-01427 while running UPDATE

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 27 Aug 2010 2:47 pm

Good afternoon/day/night, I'm starting my life in the Oracle world and to having a little trouble kind of boring, but I hope you can help me.

I'm developing a script where has a column in a table that I'm not able to do the upgrade.

In this column should be stored the somatorio of items sold by all sellers of a given item, however when I run the error ORA-01427: single-row subquery returnsmore then joins row select pure wheel correctly, but when I use within the update of pau. I googled on several Web sites, reolhei and other test structure but don't pan out. How can I do to so do this upgrade in the field?

Follows abeixo the code: UPDATE SET sales units = (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 sales);

This missing inserting or removing anything? :?::?::?: Thanks ai for the attention! : wink:
zedd
Location: Bahia

Poston Fri, 27 Aug 2010 2:51 pm

Error: ORA-01427: single-row subquery returns more than one row Cause: You tried to execute an SQL statement that contained a 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 functions against your subquery results: * ANY * ALL * IN * NOT IN For example, if you tried to run the following SQL statement: 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%20:%20.%20techonthenet%20.%20com/oracle/errors/images/ora01427_001%20.%20png[/img] The most common way to correct this SQL statement is to use the IN function as follows: select * from orders where supplier_id IN (select supplier_id from suppliers where supplier_name = ' IBM ');


[url = : http//www techonthenet com . ./oracle/php errors/. ora01427] source [/url]
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Fri, 27 Aug 2010 2:54 pm


PL/SQL: How to update using select This \"problem\" came across 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 some background searchs I'vê adopted this method:
Code: Select all
UPDATE TABLEA SET
(TABLEA.COLUMNA, TABLEA.COLUMNB) =
(SELECT TABLEB.COLUMNA, TABLEB.COLUMNB FROM TABLEB WHERE TABLEB.ID = TABLEA.ID)
I'vê basically of one inner select and join the two tables in the inner select statement's WHERE clause.
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Fri, 27 Aug 2010 3:33 pm

This occurring to your table sales has more than one record and has no where in the subquery.

To resolve you should rewrite so that the subquery returns only one record.

If the expected result is a change in all the table rows sales you can do like this:
Code: 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 PK fields that I put the columns of your PK.
gfkauer
Location: Sapiranga - RS

Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

Poston Fri, 27 Aug 2010 4:37 pm

Thanks for the attention of everyone who posted but analyzing with calmer detect where the problem was, before the expected result, and then I saw what I was doing wrong.

In fact I didn't have to do a subquery, since I wanted the sum of columns that contain values, it was only necessary to do an update.

I traveled legally and ran over the solution without need. Follow fix
Code: 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))
JUST LIKE that:-

Thanks for the cooperation of all:D
zedd
Location: Bahia


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 3 guests

cron