[Vertica][VJDBC](5569) ERROR:Either column "update_ts" does not exist or table alias "update_ts
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
0
Comments
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?
Works in DbVisualizer too: