Self join not permitted in Updates
Navin_C
Vertica Customer ✭
Hello all, As we know, self joins are not permitted int he update query, Do we have any workaround for this I have a query which updates the target table based on a join condition Code: Select all Update target_table set x= y from target_table tgt,(select column1 from target_table t, table2 t2, table3 t3 where t.a=t2.b qualify row_number() over( partition by t.column_s order by t2.column_h desc) =1 )stg where tgt.column_g="value" ; This says self join is not allowed in updates How do we achieve this.
0
Comments
any update on this, as still today i'm getting this error:
I'm Using Vertica Analytic Database v6.1.2-0
Still true in Vertica 9.2:
But you could do this:
Although, why not just run this?
Thanks for the update, I have solved this with MERGE statement
Your first solution will also work.
My use case was to add a column in table
which will have data from previous row like below:
Before update:
c1 c2
1 NULL
2 NULL
3 NULL
4 NULL
After update:
c1 c2
1 NULL
2 1
3 2
4 3