Self join not permitted in Updates

Navin_CNavin_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.

Comments

  • Hi Navin, This is a known issue and JIRA has been already issued. At this moment I do not have a ETA. Regards, Bhawana
  • 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
  • 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
  • any update on this, as still today i'm getting this error:
    I'm Using Vertica Analytic Database v6.1.2-0

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited 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)
    
  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file