Oracle: update using values from other tables

Question

I've got the following Update but I don't want to copy the values manually. I prefer to extract them using a query. Should I use PL/SQL or is there an SQL formulation?

The query that I use to obtain the values I've copied manually into the update is not key preserving ("Key-preserved table concept in join view").

UPDATE wkf_cronologia
   SET swkf_stato_workflow_id = 'o3gE1tlSdcDIC6FF',
       swkf_data_ini = TO_TIMESTAMP ('19-06-2010 18:28:10,556000000','DD-MM-RRRR HH24:MI:SS,FF'),
       swkf_versione = 0, 
       SPWKF_STATO_PUBBLICO_ID = '*1UNICOO',
       SPWKF_DATA_INI = TO_TIMESTAMP ('01-01-0001 00:00:00,000000000', 'DD-MM-RRRR HH24:MI:SS,FF'),
       SPWKF_VERSIONE = 0
 WHERE wkfc_cronologia_id = 'ApAJ0qCudNphjLxj';

Are you stating that `wkf_cronologia` is a view? And that the view is not key-preserved? That implies that Oracle cannot determine which row(s) in one or more of the underlying tables to update when you issue an update statement. Can you make the view key-preserved? Can you update the base table? Can you create an `INSTEAD OF UPDATE` trigger on the view that can figure out which rows in the table you want updated? — Justin Cave, Feb 07 '12 at 12:28
possible duplicate of [Update rows in one table with data from another table based on one column in each being equal ](http://stackoverflow.com/questions/7918688/update-rows-in-one-table-with-data-from-another-table-based-on-one-column-in-eac) — Allan, Feb 07 '12 at 13:58
For Justin Cave: No, wkf_cronologia is a table. Maybe you can bettere understand reading my previous post: http://stackoverflow.com/questions/9164586/oracle-multiple-table-updates-ora-01779-cannot-modify-a-column-which-maps-t/9166046#9166046I — Revious, Feb 07 '12 at 20:27

score 1 · Answer 1 · answered Feb 07 '12 at 12:33

You can use a subquery:

UPDATE wkf_cronologia
   SET (swkf_stato_workflow_id,
       swkf_data_ini,
       swkf_versione, 
       SPWKF_STATO_PUBBLICO_ID,
       SPWKF_DATA_INI,
       SPWKF_VERSIONE) = (select a,b,0,c,d,e
                            from another_table
                            where something=wkf_cronologia.swkf_stato_workflow_id)
 WHERE swkf_versione is null;

Oracle: update using values from other tables

1 Answers1