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

Forum|Forum|7 years ago
April 22, 2019
2 replies
10 views

+1

esangrame
Participating Frequently

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

+2

Jim_Knicely
Participating Frequently
Forum|Forum|7 years ago
April 22, 2019

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?