Merge on table having Identity Column

kapil9186kapil9186 Registered User

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 Employee, Registered User, VerticaExpert
    edited June 19

    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)
    
  • kapil9186kapil9186 Registered User

    So Does this means that vertica do not support merge insert for tables having identity column in it.?

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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"

Leave a Comment

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