1

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.

  1. Creating a table using the inner query.
  2. 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?

2

Am I abusing the with statement?

No, you are abusing the UPDATE statement. With an UPDATE statement, the correlated subquery is executed for each row from final_table.

Updating values from an other table should be typically done with MERGE.

Example:

merge into final_table using
(
select c4,c5,c6
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
)
) inner
on inner.c6 = final_table.primary_key
when matched then update set final_table.destination_col1 = inner.c4, 
                             final_table.destination_col2 = inner.c5;
  • Tried with the merge. That still hangs forever. – Kannan Ramamoorthy Mar 27 '18 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.