CODEWITHSUNDEEP
sqloracle-database
troyz
troyz

Reputation: 1395

Oracle SQL Insert external values

I'm having problems to add some values to a table. The query that i'm using is this one: 

INSERT INTO ACTUAR
(CAPITULO,TEMPORADA,COD_GRUPO,COD_LOC)
SELECT NUM_CAP,NUM_TEMPO,CODIGO_GRUPO,CODIGO_LOC
FROM COMENTAR
MINUS
SELECT CAPITULO,TEMPORADA,COD_GRUPO, COD_LOC
FROM ACTUAR;

The ACTUAR table has 2 more values, which I want to add from a value on COMENTAR like this:

NVL(SUBSTR(COMENTARIO,0,10),'SIN PROBLEMA'),
NVL(SUBSTR(COMENTARIO,LENGTH(COMENTARIO)-4),'SIN SOLUCION')

Actuar table

CAPITULO,TEMPORADA,COD_GRUPO,COD_LOC,PROBLEMA,SOLUCION

Comentar table

NUM_CAP,NUM_TEMPO,CODIGO_GRUPO,CODIGO_LOC,COMENTARIO

I want to put the first 10 chars of COMENTARIO on ACTUAR'S PROBLEMA and the last 4 on ACTUAR'S SOLUCION or the text in the nvl if they are null.

I want to insert the result of the minus operation on ACTUAR plus the 10 first chars of COMENTARIO from COMENTAR into ACTUAR'S PROBLEMA and the last 4 on ACTUAR'S SOLUCION

The desired result should be something like

ACTUAR

CAPITULO,TEMPORADA,COD_GRUPO, and COD_LOC the values from the minus operation.

PROBLEMA - first 10 chars of COMENTAR COMENTARIO

SOLUCION - last 4

Upvotes: 0

Views: 146

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

This solution worked for me. I used merge statement, without update clause.

merge into actuar a
using (
  select num_cap, num_tempo, codigo_grupo, codigo_loc, 
      nvl(substr(comentario,1,10),'SIN PROBLEMA') prob,
      nvl(substr(comentario, greatest(length(comentario)-4, 1)),'SIN SOLUCION') sol
    from comentar) c
on (a.capitulo = c.num_cap and a.temporada = c.num_tempo
  and a.cod_grupo = c.codigo_grupo and a.cod_loc = c.codigo_loc)
when not matched then insert (a.capitulo, a.temporada, a.cod_grupo, 
    a.cod_loc, a.problema, a.solucion)
  values (c.num_cap, c.num_tempo, c.codigo_grupo, c.codigo_loc, c.prob, c.sol)

Upvotes: 1

Related Questions