[Resolvido] Update com multiplas linhas

[Douglas Tondo 0]

Boa tarde pessoal, tenho um update e PRECISO que ele atualize 13 linhas de uma vez, mas não estou conseguindo, porque está dando o seguinte erro:

 

Error report:

ORA-01427: a subconsulta de uma única linha retorna mais de uma linha

ORA-06512: em "DBO_CENTAURUS_INTECNIAL.SP_ATUALIZA_METAS_INTECNIAL", line 54

ORA-06512: em line 1

01427. 00000 - "single-row subquery returns more than one row"

 

o Código:

------------------------------------------------------------------------------------------------------------------------------

UPDATE mt_dados

SET ( VL_RE_01,

VL_RE_02,

VL_RE_03,

VL_RE_04,

VL_RE_05,

VL_RE_06,

VL_RE_07,

VL_RE_08,

VL_RE_09,

VL_RE_10,

VL_RE_11,

VL_RE_12 ) = ( SELECT MT_REAL.VL_RE_01,

MT_REAL.VL_RE_02,

MT_REAL.VL_RE_03,

MT_REAL.VL_RE_04,

MT_REAL.VL_RE_05,

MT_REAL.VL_RE_06,

MT_REAL.VL_RE_07,

MT_REAL.VL_RE_08,

MT_REAL.VL_RE_09,

MT_REAL.VL_RE_10,

MT_REAL.VL_RE_11,

MT_REAL.VL_RE_12

 

FROM (

SELECT MT_GRUPO.CD_MT_GRUPO cd_mt_grupo,--select * from

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 01 THEN 1 ELSE NULL END) VL_RE_01,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 02 THEN 1 ELSE NULL END) VL_RE_02,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 03 THEN 1 ELSE NULL END) VL_RE_03,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 04 THEN 1 ELSE NULL END) VL_RE_04,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 05 THEN 1 ELSE NULL END) VL_RE_05,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 06 THEN 1 ELSE NULL END) VL_RE_06,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 07 THEN 1 ELSE NULL END) VL_RE_07,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 08 THEN 1 ELSE NULL END) VL_RE_08,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 09 THEN 1 ELSE NULL END) VL_RE_09,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 10 THEN 1 ELSE NULL END) VL_RE_10,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 11 THEN 1 ELSE NULL END) VL_RE_11,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 12 THEN 1 ELSE NULL END) VL_RE_12

 

FROM (agenda

JOIN mt_grupo

ON (agenda.cdempresa=mt_grupo.cd_empresa AND agenda.cdrepres=mt_grupo.cd_repres AND agenda.cdvendedor=mt_grupo.cd_vendedor)

) -- sqlserver_utilities.datepart('YEAR', data) = v_V_ANO

WHERE to_number(to_char(data,'yyyy')) = v_V_ANO

AND realizado = '1' --Realizado

AND tipo_compr = 1 --Contato Telefonico

AND mt_grupo.fl_detalhe = 1

GROUP BY MT_GRUPO.CD_MT_GRUPO ) mt_real -- NOME DESSE SELECT GIGANTE... --,CDEMPRESA,CDREPRES,CDVENDEDOR

 

JOIN mt_dados

ON ( mt_real.cd_mt_grupo = mt_dados.cd_mt_grupo )

WHERE mt_dados.ANO = v_V_ANO

AND--ANO

mt_dados.CD_MT_TIPO = 1 );

------------------------------------------------------------------------------------------------------------------------------

 

Coloquei, abaixo, o resultado da consulta do select maior (Destacado em Vermelho).

 

 

RESULTADO:

----------------------------------------------------------------------------------------------------------------------------------------

 

Caso alguém não consiga visualizar a foto: Resultado.xls(Excel)

----------------------------------------------------------------------------------------------------------------------------------------

 

Alguma idéia?? ehehe, qualquer ajuda é bem-vinda ^^

[Rômulo Silva 0]

Kra acho que é mais ou menos assim que deveria ser:

UPDATE mt_dados
SET SELECT DISTINCT mt_dados.VL_RE_01 = MT_REAL.VL_RE_01,
mt_dados.VL_RE_02 = MT_REAL.VL_RE_02,
mt_dados.VL_RE_03 = MT_REAL.VL_RE_03,
mt_dados.VL_RE_04 = MT_REAL.VL_RE_04,
mt_dados.VL_RE_05 = MT_REAL.VL_RE_05,
mt_dados.VL_RE_06 = MT_REAL.VL_RE_06,
mt_dados.VL_RE_07 = MT_REAL.VL_RE_07,
mt_dados.VL_RE_08 = MT_REAL.VL_RE_08,
mt_dados.VL_RE_09 = MT_REAL.VL_RE_09,
mt_dados.VL_RE_10 = MT_REAL.VL_RE_10,
mt_dados.VL_RE_11 = MT_REAL.VL_RE_11,
mt_dados.VL_RE_12 = MT_REAL.VL_RE_12 )
FROM (
SELECT MT_GRUPO.CD_MT_GRUPO cd_mt_grupo,--select * from
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 01 THEN 1 ELSE NULL END) VL_RE_01,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 02 THEN 1 ELSE NULL END) VL_RE_02,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 03 THEN 1 ELSE NULL END) VL_RE_03,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 04 THEN 1 ELSE NULL END) VL_RE_04,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 05 THEN 1 ELSE NULL END) VL_RE_05,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 06 THEN 1 ELSE NULL END) VL_RE_06,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 07 THEN 1 ELSE NULL END) VL_RE_07,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 08 THEN 1 ELSE NULL END) VL_RE_08,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 09 THEN 1 ELSE NULL END) VL_RE_09,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 10 THEN 1 ELSE NULL END) VL_RE_10,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 11 THEN 1 ELSE NULL END) VL_RE_11,
COUNT(CASE WHEN to_number(to_char(data,'mm')) = 12 THEN 1 ELSE NULL END) VL_RE_12

FROM (agenda
JOIN mt_grupo
ON (agenda.cdempresa=mt_grupo.cd_empresa AND agenda.cdrepres=mt_grupo.cd_repres AND agenda.cdvendedor=mt_grupo.cd_vendedor)
) -- sqlserver_utilities.datepart('YEAR', data) = v_V_ANO
WHERE to_number(to_char(data,'yyyy')) = v_V_ANO
AND realizado = '1' --Realizado
AND tipo_compr = 1 --Contato Telefonico
AND mt_grupo.fl_detalhe = 1
GROUP BY MT_GRUPO.CD_MT_GRUPO ) mt_real -- NOME DESSE SELECT GIGANTE... --,CDEMPRESA,CDREPRES,CDVENDEDOR

JOIN mt_dados
ON ( mt_real.cd_mt_grupo = mt_dados.cd_mt_grupo )
WHERE mt_dados.ANO = v_V_ANO
AND--ANO
mt_dados.CD_MT_TIPO = 1 );

[Douglas Tondo 0]

Obrigado pela ajuda Rômulo, mas está dando erro de sintaxe bem no começo lá em "SET (SELECT mt_dados.VL_RE_01 = MT_REAL.VL_RE_01....)"

Tem alguma idéia?

[Rômulo Silva 0]

Tenta esse aqui fazer um com base nesse exemplo que vou te passar...

Peguei esse exemplo em meio aos scripts que tenho na minha base.

 

Update apg
Set  vlr_re1 = x. vlr1,
	   vlr_re2 = x. vlr2
From apg, (select ger_valor.vlr1, ger_valor.vlr2 from ger_valor where ger_valor.idt_area = 1) x

[Motta 645]

O Oracle não faz update com joins , o que costumoa fazer e um bloco (ou procedure) para fazer isto.

 

Exemplo :

 

declare
begin
  for r in (select tabela1.*
			  from tabela1,tabela2
			  where tabela1.chave = tabela2.chave)
  loop
	update tabela1 
	set tabela1.campo1 = tabela2.campo1
	where tabela1.chave = r.chave;
  end loop;
end;

Ou seja a ideia básica e gerar um select com a chave e os dados a serem alterados e fazer isto via bloco.

 

Me parece que só vai rodar este update uma vez , mas caso precise que esta solução rode sempre você pode fazer uma procedure e chamar de alguma aplicação.

 

Ajudou ?

[Douglas Tondo 0]

O Oracle não faz update com joins , o que costumoa fazer e um bloco (ou procedure) para fazer isto.

 

Exemplo :

 

declare
begin
  for r in (select tabela1.*
			  from tabela1,tabela2
			  where tabela1.chave = tabela2.chave)
  loop
	update tabela1 
	set tabela1.campo1 = tabela2.campo1
	where tabela1.chave = r.chave;
  end loop;
end;

Ou seja a ideia básica e gerar um select com a chave e os dados a serem alterados e fazer isto via bloco.

 

Me parece que só vai rodar este update uma vez , mas caso precise que esta solução rode sempre você pode fazer uma procedure e chamar de alguma aplicação.

 

Ajudou ?

Ajudou sim, logicamente faz sentido, mas uma pergunta, que "Chave" é essa a qual voce está se referindo? A chave da linha?

 

Se sim, como se utiliza essa chave?

 

vlw

[Douglas Tondo 0]

Hummm...

 

Motta, ainda nao descobri o que é a chave mas imagino que isso possa substituir ...

 

olha só que tri:!

Trabalhando com linhas

**********************************************************

Usually, this can be solved with rownum. The following example retrieves the first 5 rows:

 

select name, price

from items

where rownum < 6;

 

NAME PRICE

-------------------- ----------

cup 1.2

book 49.99

mobile 89.99

coke .78

pencil 1.35

 

This was easy. But this is also where the problems start.

One problem is: how to select the rows 6 though 10? The following (naive) approach does not work:

 

select name, price

from items

where rownum > 5 and

rownum < 11;

 

no rows selected

 

This is because the pseudo column rownum never reaches 6. Rownum counts actually returned rows. In order for where rownum > 5 to be true, 5 rows must already have returned, but they are not, because these were excluded through exactly this where clause.

This dilemma can be solved with a nested select:

 

select name, price

from (

select rownum r, name, price

from items

)

where r > 5 and

r < 11;

 

NAME PRICE

-------------------- ----------

dollar 1

door 150

oracle 19999

carpet 122.4

apple 1.05

 

This works because Oracle first evaluates the inner select statement and returns all records with an increasing rownum. The outer where clause can then select the rows it needs.

However, there are more problems. The most important one seems to be: what exaclty does first mean.

For example, say, we want to retrieve the five cheapest items.

**********************************************************

[Douglas Tondo 0]

Bem, Juntando o conhecimento obtido na pagina: Trabalhando com Linhas

e com as dicas do Admin Motta consegui resolver o problema.

 

Obrigado a todos que opinaram e de alguma forma contribuíram para essa solução B)

 

Aqui segue um "Código Exemplo" para aqueles que tiverem a mesma duvida que eu futuramente..

 

***********************Código*********************************

DECLARE

Updateline NUMBER; -- variável que serve apenas para imprimir na tela usando o DBMS_OUTPUT.PUT_LINE();

inicio NUMBER := -1; -- servem para orientar a consulta

fim NUMBER := 1; -- idem.

BEGIN

DBMS_OUTPUT.ENABLE(0); -- habilita para imprimir na tela

FOR i IN 1..13 LOOP -- aqui faz um loop 13 vezes (era o que eu precisava no meu caso)

inicio := inicio + 1; --incrementando a variável

fim := fim + 1; --incrementando a variável

----------------------

/*Código sql*/ -- estou usando o "into Updateline" para armazenar o select e imprimir na tela depois.

select coluna into Updateline from (select rownum r, coluna from tabela ) -- explicação disso aqui: Trabalhando com Linhas

where r > inicio -- essa linha e a de baixo combinadas faz retornar apenas 1 resultado

and r < fim;

------------------------

DBMS_OUTPUT.PUT_LINE(Updateline); -- imprimi na tela o que está acontecendo

END LOOP; -- finaliza o loop

END;

**************************************************************

 

Bem, isso é o exemplinho..

 

Agora para que ficou curioso para ver como ficou o código final:

 

-------------------------------------------------------------------------------------------------------------------------

DECLARE

inicio NUMBER := -1;

fim NUMBER := 1;

BEGIN

--DBMS_OUTPUT.ENABLE(0);

FOR i IN 1..13 LOOP

inicio := inicio + 1;

fim := fim + 1;

/***********Começo**********************/

UPDATE mt_dados

SET ( VL_RE_01, VL_RE_02, VL_RE_03, VL_RE_04, VL_RE_05, VL_RE_06, VL_RE_07,

VL_RE_08, VL_RE_09, VL_RE_10, VL_RE_11, VL_RE_12 ) =

(

SELECT VL_RE_01, VL_RE_02, VL_RE_03, VL_RE_04, VL_RE_05, VL_RE_06, VL_RE_07,

VL_RE_08, VL_RE_09, VL_RE_10, VL_RE_11, VL_RE_12

 

FROM (SELECT ROWNUM s, MT_REAL.VL_RE_01, MT_REAL.VL_RE_02, MT_REAL.VL_RE_03,

MT_REAL.VL_RE_04, MT_REAL.VL_RE_05, MT_REAL.VL_RE_06,

MT_REAL.VL_RE_07, MT_REAL.VL_RE_08, MT_REAL.VL_RE_09,

MT_REAL.VL_RE_10, MT_REAL.VL_RE_11, MT_REAL.VL_RE_12

FROM (

SELECT MT_GRUPO.CD_MT_GRUPO cd_mt_grupo,--select * from

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 01 THEN 1 ELSE NULL END) VL_RE_01,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 02 THEN 1 ELSE NULL END) VL_RE_02,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 03 THEN 1 ELSE NULL END) VL_RE_03,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 04 THEN 1 ELSE NULL END) VL_RE_04,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 05 THEN 1 ELSE NULL END) VL_RE_05,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 06 THEN 1 ELSE NULL END) VL_RE_06,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 07 THEN 1 ELSE NULL END) VL_RE_07,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 08 THEN 1 ELSE NULL END) VL_RE_08,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 09 THEN 1 ELSE NULL END) VL_RE_09,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 10 THEN 1 ELSE NULL END) VL_RE_10,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 11 THEN 1 ELSE NULL END) VL_RE_11,

COUNT(CASE WHEN to_number(to_char(data,'mm')) = 12 THEN 1 ELSE NULL END) VL_RE_12

 

FROM (agenda

JOIN mt_grupo

ON (agenda.cdempresa=mt_grupo.cd_empresa AND agenda.cdrepres=mt_grupo.cd_repres AND agenda.cdvendedor=mt_grupo.cd_vendedor)

) -- sqlserver_utilities.datepart('YEAR', data) = v_V_ANO

WHERE to_number(to_char(data,'yyyy')) = v_V_ANO

AND realizado = '1' --Realizado

AND tipo_compr = 1 --Contato Telefonico

AND mt_grupo.fl_detalhe = 1

GROUP BY MT_GRUPO.CD_MT_GRUPO ) mt_real -- NOME DESSE SELECT GIGANTE... --,CDEMPRESA,CDREPRES,CDVENDEDOR

 

JOIN mt_dados ON ( mt_real.cd_mt_grupo = mt_dados.cd_mt_grupo )

WHERE mt_dados.ANO = v_V_ANO

AND mt_dados.CD_MT_TIPO = 1)

WHERE s > inicio

AND s < fim

);

/***********Fim**********************/

END LOOP;

END;

-------------------------------------------------------------------------------------------------------------------------

 

Ele está em dentro de uma procedure com mmais 9 montrinhos desse tamanho... O me intrigou é que esse update demora 6 segundos :o , e o select(grandão) com os joins demora apenas um segundo... :blink: ... acho fazendo uma reindexação e "analizando" a tabela deve melhorar.. o que voces acham?

 

porque 1 update demora 6 segundos, e são 9! ...logo = 56 segundos :wacko: ..Bem vou dar uma olhada... se descobrir algo antes posto aqui ... valew!

[Motta 645]

O Oracle não faz update com joins , o que costumoa fazer e um bloco (ou procedure) para fazer isto.

 

Exemplo :

 

declare
begin
  for r in (select tabela1.*
			  from tabela1,tabela2
			  where tabela1.chave = tabela2.chave)
  loop
	update tabela1 
	set tabela1.campo1 = tabela2.campo1
	where tabela1.chave = r.chave;
  end loop;
end;

Ou seja a ideia básica e gerar um select com a chave e os dados a serem alterados e fazer isto via bloco.

 

Me parece que só vai rodar este update uma vez , mas caso precise que esta solução rode sempre você pode fazer uma procedure e chamar de alguma aplicação.

 

Ajudou ?

Ajudou sim, logicamente faz sentido, mas uma pergunta, que "Chave" é essa a qual voce está se referindo? A chave da linha?

 

Se sim, como se utiliza essa chave?

 

vlw

 

A chave seria a chave (pk) da tabela1 do exemplo , a tabela a ser atualizada

 

um exemplo mais real , atualizar cliente com base em fornecedor

 

declare

begin

for r in (select cliente.cgc ,fornecedor.fone

from cliente,fornecedor

where cliente.chave = fornecedor.chave)

loop

update cliente

set cliente.fone = r.fone

where cliente.chave = r.cgc;

end loop;

end;

[Douglas Tondo 0]

O Oracle não faz update com joins , o que costumoa fazer e um bloco (ou procedure) para fazer isto.

 

Exemplo :

 

declare
begin
  for r in (select tabela1.*
			  from tabela1,tabela2
			  where tabela1.chave = tabela2.chave)
  loop
	update tabela1 
	set tabela1.campo1 = tabela2.campo1
	where tabela1.chave = r.chave;
  end loop;
end;

Ou seja a ideia básica e gerar um select com a chave e os dados a serem alterados e fazer isto via bloco.

 

Me parece que só vai rodar este update uma vez , mas caso precise que esta solução rode sempre você pode fazer uma procedure e chamar de alguma aplicação.

 

Ajudou ?

Ajudou sim, logicamente faz sentido, mas uma pergunta, que "Chave" é essa a qual voce está se referindo? A chave da linha?

 

Se sim, como se utiliza essa chave?

 

vlw

 

A chave seria a chave (pk) da tabela1 do exemplo , a tabela a ser atualizada

 

um exemplo mais real , atualizar cliente com base em fornecedor

 

declare

begin

for r in (select cliente.cgc ,fornecedor.fone

from cliente,fornecedor

where cliente.chave = fornecedor.chave)

loop

update cliente

set cliente.fone = r.fone

where cliente.chave = r.cgc;

end loop;

end;

 

obrigado motta, problema solucionado, pode fechar o post :D

[Motta 645]

Corrigindo o exemplo acima

 

declare

begin

for r in (select tabela1.*

from tabela1,tabela2

where tabela1.chave = tabela2.chave)

loop

update tabela1

set tabela1.campo1 = r.campo1

where tabela1.chave = r.chave;

end loop;

end;