Merge on table having Identity Column

I want to perform a merge insert onto a table which has Identity column. The merge will be based on another column within the target table but target table do have identity column in it.

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited June 2018

    Hi,

    Since the target table has an IDENTITY, you'll have to do the merge manually with and UPDATE statement followed by an INSERT statement.

    Example:

    dbadmin=> CREATE TABLE source_table (c1 INT, c2 VARCHAR(100));
    CREATE TABLE
    
    dbadmin=> CREATE TABLE target_table (c1 IDENTITY, c2 INT, c3 VARCHAR(100));
    CREATE TABLE
    
    dbadmin=> INSERT INTO source_table SELECT 1, 'TEST1';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO source_table SELECT 2, 'TEST2';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO source_table SELECT 3, 'TEST3';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO target_table (c2, c3) SELECT 1, 'TEST4';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> SELECT * FROM source_table ORDER BY c1;
     c1 |  c2
    ----+-------
      1 | TEST1
      2 | TEST2
      3 | TEST3
    (3 rows)
    
    dbadmin=> SELECT * FROM target_table;
     c1 | c2 |  c3
    ----+----+-------
      1 |  1 | TEST4
    (1 row)
    
    dbadmin=> MERGE INTO target_table
    dbadmin-> USING source_table
    dbadmin->    ON source_table.c1 = target_table.c2
    dbadmin->  WHEN MATCHED THEN UPDATE SET c3 = source_table.c2
    dbadmin->  WHEN NOT MATCHED THEN INSERT (c2, c3) VALUES (source_table.c1, source_table.c2);
    ERROR 4711:  Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported
    dbadmin=> Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported
    
    dbadmin=> UPDATE target_table SET c2 = source_table.c1, c3 = source_table.c2
    dbadmin->   FROM source_table
    dbadmin-> WHERE source_table.c1 = target_table.c1;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO target_table (c2, c3)
    dbadmin->   SELECT c1, c2
    dbadmin->     FROM source_table
    dbadmin->    WHERE NOT EXISTS (SELECT NULL
    dbadmin(>                        FROM target_table
    dbadmin(>                       WHERE target_table.c2 = source_table.c1);
     OUTPUT
    --------
          2
    (1 row)
    
    dbadmin=> SELECT * FROM target_table ORDER BY 2;
     c1 | c2 |  c3
    ----+----+-------
      1 |  1 | TEST1
      3 |  2 | TEST2
      2 |  3 | TEST3
    (3 rows)
    
  • So Does this means that vertica do not support merge insert for tables having identity column in it.?

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Yes. That's why in my example I got the error message: "ERROR 4711: Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported"

  • Hello
    We get same error when the MERGE is on a table having a column with a DEFAULT VALUE based on a sequence .... (not defined as Identity) . We turned to merge to avoid problems with Updates , but now we hit another problem.... Is there any workaround since 2018 (date of last post here)
    Thanks
    Isabelle

  • marcothesanemarcothesane - Select Field - Administrator

    Hi Isabelle -
    Yes , this remains the same. DEFAULT sequencename.nextval has the same behaviour as an IDENTITY column.

Leave a Comment

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