Home / Questions / 25242
Accepted
Stevn
Asked: 2020-09-28 12:31:33 +0800 CST 2020-09-28 12:31:33 +0800 CST

Insert records with select on insert

  • 772

I am trying to insert records into a table TB_DET_SUSCRIPCION_DOCbut SELECTit returns more than one record.

the error is the following.

Bug 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

2 Answers

  1. Best Answer

    The construction you use:

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

    It is used to insert a single record.

    Your problem, as the error message clearly states, is that the sub-query you execute returns more than one record, and in this 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, further refine 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 return values

    If, on the other hand, the statement is correct and what you want is to insert a record for each return value, change the construction to a 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 construct will insert as many rows as the query returns.

    • 2

  2. Use static values ​​as part of select on 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;
    • 1