self joins not permitted in Updates

[nnani]

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.

[BoMBaY]

Hi nnani,

You can use Local Temporary Tables to store a result set from a sub-query using CTAS and then update a target table from a local temporary table.

Local Temporary Tables

Local temporary tables are created in the V_TEMP_SCHEMA namespace and inserted into the user's search path transparently. Each local temporary table is visible only to the user who creates it, and only for the duration of the session in which the table is created.

When the session ends, HP Vertica automatically drops the table definition from the database catalogs. You cannot preserve non-empty, session-scoped temporary tables using the ON COMMIT PRESERVE ROWS statement.

Creating local temporary tables is significantly faster than creating regular tables, so you should make use of them whenever possible.

[nnani]

Thanks itipong,

I am doing the same.