Update a Table with results of a Query

Question

I want to update a new column on one of my tables but I'm not sure how to go about this.

The new column is MARITAL_STATUS and is on the SCV_CLEINT_DETAILS table. The values for this table will be generated from various sources and this query below gets me the values I'm looking for:

SELECT scd.qsclient_id, 
       scd.system_client_id,
       NVL(c.paxus_client_id, c.client_id),
       UPPER(NVL(NVL2(c.paxus_client_id, pc.clt_mar_stat, c.maritial_status_code), decode(c.client_type_id, '2000001702', 'C', 'U'))) AS NewMarStatus,
       scd.marital_status
FROM scv_client_details scd, client c, paxus_client pc
WHERE scd.system_client_id = to_char(c.client_id)
AND  c.paxus_client_id = pc.client(+)
AND  UPPER(scd.SYSTEM_INDICATOR) = 'WRITEN'
AND (scd.marital_status <> UPPER(NVL(NVL2(c.paxus_client_id, pc.clt_mar_stat, c.maritial_status_code), decode(c.client_type_id, '2000001702', 'C', 'U'))) OR 
     scd.marital_status IS NULL)

I would like to update the new MARITAL_STATUS column with the value generated in the NewMarStatus above. I really don't know how to write the update statement though.

Any help really appreciated.

Updating a join is possible, see: [Oracle - Update statement with inner join](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) for example. — Vincent Malgrat, May 29 '13 at 12:57
As my marital_status value can come from either the client table, the paxus_client table or generated based on the client_type_id from the client table I'm not sure how I can use a join?? Any way to update the scv_client_details table where the marital_status field is from the output from this query matching against the returned client id from the query also? — mcquaim, May 29 '13 at 13:03
Use `MERGE` as demoed in [this answer](http://stackoverflow.com/a/2446859/119634). `MERGE INTO scv_client_details d USING (your_query) v ON v.rowid=d.rowid ...`. — Vincent Malgrat, May 29 '13 at 13:24
It said that I can' use the rowid from the query (v.rowid above) so I changed it to be (v.system_client_id=d.system_client_id) but I get two errors. (ORA-30926 & ORA-02063). Not sure if this is possible?? — mcquaim, May 29 '13 at 13:43
You have to add a column that gets the rowid from your table, as Quassnoi did in his example. Some things are *meant* to be left as an exercise! — Vincent Malgrat, May 29 '13 at 14:19

score 1 · Answer 1 · answered May 29 '13 at 13:17

I always use a bulk upload procedure for these things.

declare
    cursor cur
    is
    SELECT scd.rowid row_id,
           UPPER(NVL(NVL2(c.paxus_client_id, pc.clt_mar_stat, c.maritial_status_code), decode(c.client_type_id, '2000001702', 'C', 'U'))) AS marital_status
    FROM   scv_client_details scd, client c, paxus_client pc
    WHERE  scd.system_client_id = to_char(c.client_id)
    AND    c.paxus_client_id = pc.client(+)
    AND    UPPER(scd.SYSTEM_INDICATOR) = 'WRITEN'
    AND    (scd.marital_status <> UPPER(NVL(NVL2(c.paxus_client_id, pc.clt_mar_stat, c.maritial_status_code), decode(c.client_type_id, '2000001702', 'C', 'U'))) OR 
            scd.marital_status IS NULL)
    order by row_id
    ;

    type type_rowid_array is table of rowid index by binary_integer;
    type type_marital_status_array is table of scv_client_details.marital_status%type;

    arr_rowid type_rowid_array;
    arr_marital_status type_marital_status_array;

begin
    open cur;

    loop
        fetch cur bulk collect into arr_rowid, arr_marital_status;

        forall i in arr_rowid.first .. arr_rowid.last
            update scv_client_details tab
            SET    tab.marital_status = arr_marital_status(i)
            where  tab.rowid = arr_rowid(i)
            ;

        exit when cur%notfound;
    end loop;

    close cur;
    commit;

exception
  when others
    then rollback;
         raise_application_error(-20000, 'Fout bij uitvoeren update van scv_client_details(marital_status) - '||sqlerrm);
end;

Thanks for that. I was thinking of using a cursor but only if I couldn't have a simple update query. If a query is not possible then I will go down this route, thanks! — mcquaim, May 29 '13 at 13:23

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Several ways to update a join are described in this other SO:

Oracle - Update statement with inner join

MERGE is one of the easiest way to do a mass-join-update. It scales very well since it can use all forms of joins, especially HASH JOIN.

The USING clause is your query (with the addition of the rowid obviously):

MERGE INTO scv_client_details d
USING (SELECT NVL(c.paxus_client_id, c.client_id),
              UPPER(NVL(NVL2(c.paxus_client_id, 
                             pc.clt_mar_stat, 
                             c.maritial_status_code),
                        decode(c.client_type_id, '2000001702', 'C', 'U'))) 
              AS NewMarStatus,
              scd.ROWID rid
         FROM scv_client_details scd, client c, paxus_client pc
        WHERE scd.system_client_id = to_char(c.client_id)
          AND c.paxus_client_id = pc.client(+)
          AND (scd.marital_status <> 
               UPPER(NVL(NVL2(c.paxus_client_id, 
                             pc.clt_mar_stat, 
                             c.maritial_status_code),
                        decode(c.client_type_id, '2000001702', 'C', 'U')))
              OR
              scd.marital_status IS NULL)) v
ON (d.ROWID = v.rid)
WHEN MATCHED THEN
   UPDATE SET d.marital_status = newmarstatus;

See this SQLFiddle for a full example.

Thanks, that worked a treat.. If this worked I'm not sure why my ON (d.system_client_id = v.system_client_id) didn't work? Anyway, thanks a mill for the help, really appreciated.. — mcquaim, May 29 '13 at 14:41
You would need the primary key of `scv_client_details` and `system_client_id` seems to be the PK of `client`. — Vincent Malgrat, May 29 '13 at 14:53

Update a Table with results of a Query

2 Answers2