Vertica, Merge and CTE

Question

I need to do Merge in Vertica. Can I use both merge and CTE in Vertica at the same time? Example below:

WITH cte AS
(

   SELECT id,
          name
     FROM [TableA]
)

MERGE INTO [TableA] AS A
     USING cte
        ON cte.ID = A.id

      WHEN MATCHED 
      THEN UPDATE
       SET A.name = cte.name

      WHEN NOT MATCHED 
      THEN INSERT
    VALUES(cte.name);

Answer 1

Does not work as you suggested. And for an insert you need all columns for the target.

Using on a table of mine:

WITH
cte AS (
  SELECT
    id
  , UPPER(fname) AS fname
  , UPPER(lname) AS lname
  , hire_dt + 7 AS hire_dt
  FROM public.foo
)
MERGE
INTO public.foo t
USING cte s
  ON s.id = t.id
WHEN MATCHED THEN UPDATE SET
  fname   = s.fname
, lname   = s.lname
, hire_dt = s.hire_dt
WHEN NOT MATCHED THEN INSERT
 VALUES (
  s.id
, s.fname
, s.lname
, s.hire_dt
);
-- out ERROR 4856:  Syntax error at or near "MERGE" at character 130
-- out LINE 10: MERGE
-- out          ^

But what would you need it for, if you can put the full-select into the USING() clause?

MERGE
INTO public.foo t
USING (
  SELECT
    id
  , UPPER(fname) AS fname
  , UPPER(lname) AS lname
  , hire_dt + 7 AS hire_dt
  FROM public.foo
) s
  ON s.id = t.id
WHEN MATCHED THEN UPDATE SET
  fname   = s.fname
, lname   = s.lname
, hire_dt = s.hire_dt
WHEN NOT MATCHED THEN INSERT 
 VALUES (
  s.id
, s.fname
, s.lname
, s.hire_dt
);
-- out  OUTPUT 
-- out --------
-- out      42
-- out (1 row)