Self join not permitted in Updates

Navin_C · June 2013

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.

Bhawana · June 2013

Hi Navin, This is a known issue and JIRA has been already issued. At this moment I do not have a ETA. Regards, Bhawana

Sajan_Sangraula · June 2013

Hi Navin, try with a subquery instead of directly using the target table in the select list, so your update becomes: set x=y from (select * from target_table) tgt ... /Sajan

Itipong_Chewinp · July 2013

Hi Navin, 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 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.Local Temporary Tables

parthmalhan · March 2019

any update on this, as still today i'm getting this error:
I'm Using Vertica Analytic Database v6.1.2-0

Jim_Knicely · March 2019

Still true in Vertica 9.2:

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.2.0-4
(1 row)

dbadmin=> SELECT * FROM test;
 c1
----
  1
  2
(2 rows)

dbadmin=> UPDATE test
dbadmin->    SET c1 = t2.c1 * 2
dbadmin->   FROM test t2
dbadmin->  WHERE t2.c1 = test.c1
dbadmin->    AND test.c1 = 2;
ERROR 4680:  Self joins in UPDATE statements are not allowed
DETAIL:  Target relation public.test also appears in the FROM list

But you could do this:

dbadmin=> UPDATE test
dbadmin->    SET c1 = t2.c1 * 2
dbadmin->   FROM (SELECT * FROM test) t2
dbadmin->  WHERE t2.c1 = test.c1
dbadmin->    AND test.c1 = 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test;
 c1
----
  1
  4
(2 rows)

Although, why not just run this?

dbadmin=> UPDATE test SET c1 = c1 * 2 WHERE c1 = 4;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test;
 c1
----
  1
  8
(2 rows)

parthmalhan · March 2019

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

Self join not permitted in Updates

Comments

Leave a Comment