CODEWITHSUNDEEP
oracle-database
Marco Mura
Marco Mura

Reputation: 582

Oracle update subquery of records

I need to update a list of record given by a subquery, sniffing around on the web I tried this structure:

UPDATE
  (
    SELECT
      a.COL1
    FROM
      TABLE1 a,
      TABLE2 b
    WHERE
      a.field1 = b.field1
  ) update_tbl
SET
  update_tbl.COL1 = 'VALUE'

But it returns to me this Oracle error:

-> ORA-01779: Cannot modify a column which maps to a non key-preserved table

My query is the following one:

UPDATE 
    (
        SELECT 
            imp.*
        FROM table1 imp
        JOIN table2 sp ON imp.id_p = sp.id_p
        JOIN table3 cs ON sp.id_s = cs.id_s
        JOIN table4 cb ON cb.id_c = cs.id_c
        WHERE
        imp.id_b = cb.id_b
        AND (
                (to_char(imp.p,'yyyymm') < to_char(cb.data_in,'yyyymm')) OR
                (cb.data_fi IS NOT NULL AND to_char(imp.p,'yyyymm') > to_char(cb.data_fi,'yyyymm'))
            )
        and (
                (imp.v is not null) or
                (imp.v_s is not null and imp.v_s <> 0) or
                (imp.imp_co is not null and imp.imp_co <> 0) or
                (imp.imp_acc is not null and imp.imp_acc <> 0) 
                )
    ) i
    SET
        i.v = null,
        i.v_s = 0,
        i.imp_co = 0,
        i.imp_acc = 0,
        i.ID_S_CONT = 'N',
        i.ID_T_COMP = 'P',
        i.date_upd = SYSDATE,
        i.user_upd = 'SeR'

The subquery return 82 rows (tested now), and I do want to modify only that rows, What am I doing wrong?

Upvotes: 0

Views: 66

Answers (1)

Exhausted
Exhausted

Reputation: 1885

I think you are updating to imp table. so you can try MERGE like below

       MERGE INTO 
       IMP A
    USING 
       ( SELECT 
                imp.*
            FROM table1 imp
            JOIN table2 sp ON imp.id_p = sp.id_p
            JOIN table3 cs ON sp.id_s = cs.id_s
            JOIN table4 cb ON cb.id_c = cs.id_c
            WHERE
            imp.id_b = cb.id_b
            AND (
                    (to_char(imp.p,'yyyymm') < to_char(cb.data_in,'yyyymm')) OR
                    (cb.data_fi IS NOT NULL AND to_char(imp.p,'yyyymm') > 
to_char(cb.data_fi,'yyyymm'))
                )
            and (
                    (imp.v is not null) or
                    (imp.v_s is not null and imp.v_s <> 0) or
                    (imp.imp_co is not null and imp.imp_co <> 0) or
                    (imp.imp_acc is not null and imp.imp_acc <> 0) 
                    )) B

    ON (A.ID =B.ID)
    WHEN MATCHED THEN 
    UPDATE SET 
    A.v = null,
            A.v_s = 0,
            A.imp_co = 0,
            A.imp_acc = 0,
            A.ID_S_CONT = 'N',
            A.ID_T_COMP = 'P',
            A.date_upd = SYSDATE,
            A.user_upd = 'SeR'

Will update the table given by subquery. here A.ID =B.ID use the primary key.

Upvotes: 1

Related Questions