I am trying to write an update statement, which utilizes the result of a series of aggregations of
with statement as below.
update final_table set(destination_col1,destination_col2)= ( select c4,c5 from ( with temp_table1(c1,c2,c3) as select c7,c7,c9 from source_table, temp_table2(c4,c5,c6) as select "some aggregation on c1,c2",c3 from temp_table select * from temp_table2 ) where c6= final_table.primary_key );
This runs forever.
Whereas, when I try to break the same into 2 queries it works.
- Creating a table using the inner query.
Use the above-created table to update like,
update final_table set(destination_col1,destination_col2)= (select c4,c5 from step1_table where c6=final_table.primary_key)
As suggested here, I tried to check if there are any locks. But I don't have any.
Am I abusing the
with statement? Why exactly my expectation is not met?