[Vertica][VJDBC](5569) ERROR:Either column "update_ts" does not exist or table alias "update_ts

esangrameesangrame Registered User
edited April 22 in Vertica Forum

Hi ,

I'm getting error while executing below Merge.
Please guide me.

Is it only allowed to update target column from Source column and not even current_timestamp.

[Vertica]VJDBC ERROR: Either column "update_ts" does not exist or table alias "update_ts" is not allowed in "WHEN MATCHED THEN UPDATE SET" [SQL State=42602, DB Errorcode=5569]
1 statement failed.

MERGE /*+ DIRECT */  INTO schema.target_A tgt
USING schema.stg_dup_rem src ON src.id = tgt.id
WHEN MATCHED THEN UPDATE
  SET a = src.a,
      b = src.b,
      update_ts = current_timestamp 
WHEN NOT MATCHED THEN
  INSERT
  VALUES
  (
    src.a,
    src.b,
    src.id,
    current_timestamp  
  ) ;

Executing below query without column 'update_ts' in UPDATE and without current_timestamp in INSERT works fine

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited April 22

    Works okay for me in vsql:

    dbadmin=> SELECT * FROM target_A;
     id | a | b |         update_ts
    ----+---+---+----------------------------
      1 | 1 | 1 | 2019-01-12 08:17:16.014038
    (1 row)
    
    dbadmin=> SELECT * FROM stg_dup_rem;
     id | a | b
    ----+---+---
      1 | 0 | 0
      3 | 3 | 3
    (2 rows)
    
    dbadmin=> MERGE /*+ DIRECT */  INTO target_A tgt
    dbadmin-> USING stg_dup_rem src ON src.id = tgt.id
    dbadmin-> WHEN MATCHED THEN UPDATE
    dbadmin->   SET a = src.a,
    dbadmin->       b = src.b,
    dbadmin->       update_ts = current_timestamp
    dbadmin-> WHEN NOT MATCHED THEN
    dbadmin->   INSERT
    dbadmin->   VALUES
    dbadmin->   (
    dbadmin(>     src.a,
    dbadmin(>     src.b,
    dbadmin(>     src.id,
    dbadmin(>     current_timestamp
    dbadmin(>   ) ;
     OUTPUT
    --------
          2
    (1 row)
    
    dbadmin=> SELECT * FROM target_A;
     id | a | b |         update_ts
    ----+---+---+---------------------------
      1 | 0 | 0 | 2019-04-22 08:17:43.05665
      3 | 3 | 3 | 2019-04-22 08:17:43.05665
    (2 rows)
    

    Are you running this in a tool like DbVisualizer?

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Works in DbVisualizer too:

Leave a Comment

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