Insert records with select in insert

Question

Insert records with select in insert

Navigation

#1 by (2 votes)
#2 by (1 votes)

4

I am trying to insert records in a TB_DET_SUSCRIPCION_DOC table but the SELECT returns more than one record.

the error is as follows.

Error Report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

The query:

INSERT INTO cuentas.TB_DET_SUSCRIPCION_DOC (id_det_suscripcion_doc, id_suscripcion_doc, id_tip_producto, rango_envio) 
VALUES (SQ_DET_SUSCRIPCION_DOC.NextVal,(SELECT cuentas.tb_suscripcion_doc.id_suscripcion_doc 
FROM cuentas.TB_SUSCRIPCION_DOC WHERE TRUNC(fec_inicio_envio,'DD') BETWEEN to_date('01-07-2016', 'DD-MM-YYYY') AND to_date('31-07-2016', 'DD-MM-YYYY') 
AND cuentas.tb_suscripcion_doc.id_tip_doc_susc = 23 
AND cuentas.tb_suscripcion_doc.fec_termino_envio is null), 0,0);

Has anyone had that problem? Can you tell me how to solve it?

oracle sqldeveloper

asked by Stevn 27.09.2016 в 22:31

source

2 answers

1

Use static values as part of the selection in false columns

INSERT INTO cuentas.TB_DET_SUSCRIPCION_DOC (id_det_suscripcion_doc, id_suscripcion_doc, id_tip_producto, rango_envio) 
SELECT SQ_DET_SUSCRIPCION_DOC.NextVal,cuentas.tb_suscripcion_doc.id_suscripcion_doc
, 0,0 
FROM cuentas.TB_SUSCRIPCION_DOC WHERE TRUNC(fec_inicio_envio,'DD') BETWEEN to_date('01-07-2016', 'DD-MM-YYYY') AND to_date('31-07-2016', 'DD-MM-YYYY') 
AND cuentas.tb_suscripcion_doc.id_tip_doc_susc = 23 
AND cuentas.tb_suscripcion_doc.fec_termino_envio is null;

answered by 27.09.2016 в 22:42

Problem when passing a long data to a service on Android Data passing from one pointer to another

score 2 · Accepted Answer

The construction you use:

insert into Table (Fields) values (x, y);

Used to insert a single record.

Your problem, as the error message clearly says, is that the sub-query that you run returns more than one record and in such case, Oracle would not know which of those records is relevant, and therefore returns the error.

Your case has two possible solutions:

That the sub-query returns a single record

Since there are several, it fine-tunes the where clause so that it returns only one. If it is correct to return several and the decision is arbitrary, you have ways to force the engine to return a single record, for example:

INSERT INTO cuentas.TB_DET_SUSCRIPCION_DOC (
    id_det_suscripcion_doc
  , id_suscripcion_doc
  , id_tip_producto
  , rango_envio
) 
VALUES (
    SQ_DET_SUSCRIPCION_DOC.NextVal
  , (SELECT cuentas.tb_suscripcion_doc.id_suscripcion_doc 
       FROM cuentas.TB_SUSCRIPCION_DOC 
      WHERE TRUNC(fec_inicio_envio,'DD') BETWEEN to_date('01-07-2016', 'DD-MM-YYYY') AND to_date('31-07-2016', 'DD-MM-YYYY') 
        AND cuentas.tb_suscripcion_doc.id_tip_doc_susc = 23 
        AND cuentas.tb_suscripcion_doc.fec_termino_envio is null)
        and ROWNUM = 1
  , 0
  , 0);

Insert all returned values

If, on the other hand, the statement is correct and you want to insert a record for each value returned, change the construction to insert / select , like this:

INSERT INTO cuentas.TB_DET_SUSCRIPCION_DOC (
    id_det_suscripcion_doc
  , id_suscripcion_doc
  , id_tip_producto
  , rango_envio
) 
SELECT   SQ_DET_SUSCRIPCION_DOC.NextVal 
       , cuentas.tb_suscripcion_doc.id_suscripcion_doc 
       , 0
       , 0
  FROM cuentas.TB_SUSCRIPCION_DOC 
 WHERE TRUNC(fec_inicio_envio,'DD') BETWEEN to_date('01-07-2016', 'DD-MM-YYYY') AND to_date('31-07-2016', 'DD-MM-YYYY') 
   AND cuentas.tb_suscripcion_doc.id_tip_doc_susc = 23 
   AND cuentas.tb_suscripcion_doc.fec_termino_envio is null);

This construction will insert as many rows as the query returns.