update select query in oracle error

Question

i will be specific. I am trying to make an update with a select, but sql development throws me an error. The query is this:

    UPDATE(SELECT I.FECHA_EFECTIVA, I.FECHA_FIN, T.FECHA_EFECTIVA FECHA_EFEC_TEMA, T.FECHA_FIN FECHA_FIN_TEMA
FROM TA_SEFE_POND_INDCS_FINANS I
LEFT JOIN TA_SEFE_MATR_TEMAS_FINANS T
  ON I.TEMA_ID = T.TEMA_ID
LEFT JOIN TA_SEFE_MATR_FINAN M
  ON M.MATR_FINAN_ID = T.MATR_FINAN_ID)
set FECHA_EFECTIVA = FECHA_EFEC_TEMA,
    FECHA_FIN = FECHA_FIN_TEMA;

The error is "unknown command".

Strangely the select statement works fine, the problem might be the update at the end :S

Sorry for my bad english .__.

darkone2k4 · Accepted Answer · 2015-05-18 20:47:09Z

1

Use this:

    MERGE INTO TA_SEFE_POND_INDCS_FINANS T1
USING
(
    SELECT
        T.TEMA_ID       TEMA
        ,T.FECHA_EFECTIVA   FECHA_EFEC_TEMA
                ,T.FECHA_FIN        FECHA_FIN_TEMA
    FROM
        TA_SEFE_MATR_TEMAS_FINANS T
    LEFT JOIN
                TA_SEFE_MATR_FINAN M
    ON
            M.MATR_FINAN_ID = T.MATR_FINAN_ID
) T2
ON
(
    T1.TEMA_ID = T2.TEMA
)
WHEN MATCHED THEN
UPDATE SET
    T1.FECHA_EFECTIVA = T2.FECHA_EFEC_TEMA
        ,T1.FECHA_FIN     = T2.FECHA_FIN_TEMA;

answered May 18, 2015 at 20:47

darkone2k4

192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Politank-Z · Accepted Answer · 2015-05-18 20:40:22Z

0

You can't update the results of SELECT. I think this is what you're after:

WITH (SELECT i.fecha_efectiva, i.fecha_fin,
      t.fecha_efectiva fecha_efec_tema, t.fecha_fin fecha_fin_tema
      FROM ta_sefe_pond_indcs_finans i
      LEFT JOIN ta_sefe_matr_temas_finans t
        ON i.tema_id = t.tema_id
      LEFT join ta_sefe_matr_finan m
        ON m.matr_finan_id = t.matr_finan_id) a
UPDATE ta_sefe_pond_indcs_finans i
SET i.fecha_efectiva = a.fecha_efec_tema,
    i.fecha_fin = a.fecha_fin_tema
WHERE i.fecha_efectiva = a.fecha_efectiva AND i.fecha_fin = a.fecha_fin;

This uses your query for the update, but targets the update to your table instead of a query.

answered May 18, 2015 at 20:40

Politank-Z

3,7433 gold badges27 silver badges28 bronze badges

Collectives™ on Stack Overflow

update select query in oracle error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related