Running oracle update query in a loop

Question

I have a table with a lot of rows. I need to update specific column in all the rows in the table using another (very complicated) query.

This is my table:

   col1    col2
    A        null 
    B        null 
    C        null

I have a query for updating each row separately, it looks like this:

update table1 set col2='
... some query that uses other tables and the A value ...' 
where col1='A'

I want to update all the rows in one query (otherwise I will have to run the query for each row and change the variables).

I need to have the value from col1 available to be used in the inner query that I run. How can I do that?

Thanks!

Lukasz Szozda · Accepted Answer · 2019-02-20 22:31:16Z

1

There is no need for loop. You could use correlated subquery:

UPDATE table1
SET col2 = (SELECT colX FROM tableA WHERE table1.some_col = tableA.some_col)
WHERE col1='A';
-- subquery has to return single value(1 row and 1 column)

Using subqueries

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. The parent statement can be a SELECT, UPDATE, or DELETE statement in which the subquery is nested. A correlated subquery conceptually is evaluated once for each row processed by the parent statement.

answered Feb 20, 2019 at 22:31

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-02-20 22:31:08Z

0

You seem to want a correlated subquery:

update table1 t1
    set col2 = (select . . .
                from . .  .
                where . . .
                      ? = t1.col1  -- your condition goes here
                . . .
               );

answered Feb 20, 2019 at 22:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Running oracle update query in a loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related