CODEWITHSUNDEEP
oracle-databaseplsqlsql-update
Revious
Revious

Reputation: 8146

Oracle: update using values from other tables

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';

Upvotes: 0

Views: 532

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

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;

Upvotes: 1

Related Questions