CODEWITHSUNDEEP
sqlplsqloracle11g
Howie
Howie

Reputation: 2778

Converting multiple UPDATEs in a FOR..LOOP to a single SELECT/UPDATE statement

I'd like to optimize the following PL/SQL statement to a single SELECT+UPDATE SQL statement, if possible.

--Key is a VARCHAR2, Value is a CLOB
FOR Pair IN (select Key, Value from PairTable) 
LOOP           
  update UpdatableTable
  set CLOBColumn = CLOBColumn || Pair.Value
  where ID in
    (select ID from UpdatableTable
    where CONTAINS("indexedcolumns", '{' || Pair.Key || '}') > 0); 
  commit;
END LOOP;

The problem is that I need to use a part of result from the UPDATE's WHERE clause in the same UPDATE's SET clause. Conceptually, I want to first SELECT all of the IDs in the PairTable. Then use the Key string to see if it's contained in the UpdatableTable. Then set the Value string (that corresponds with the aforementioned Key string) to the UpdatableTable's CLOBColumn.

Upvotes: 0

Views: 274

Answers (2)

Dileep
Dileep

Reputation: 664

you can use the below update statements

update UpdatableTable
  set CLOBColumn = CLOBColumn || Value from PairTable
  where ID in
    (select ID from UpdatableTable
    where CONTAINS("indexedcolumns", '{' || Pair.Key || '}') > 0); 

or

update UpdatableTable
  set CLOBColumn = CLOBColumn || b.Value from  (select Key, Value from PairTable) b
  where ID in
    (select ID from UpdatableTable
    where CONTAINS("indexedcolumns", '{' || Pair.Key || '}') > 0);

Upvotes: 0

Plouf
Plouf

Reputation: 627

At least use a FORALL in PLSQL.

Upvotes: 1

Related Questions