CODEWITHSUNDEEP
sqloracle-databaseloops
Rea
Rea

Reputation: 3

Running oracle update query in a loop

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!

Upvotes: 0

Views: 143

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You seem to want a correlated subquery:

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

Upvotes: 0

Related Questions