Cannot use outer join in update syntax

Vertica Analytic Database v11.0.1-1

Hi yashiro,

Your joins provides no benefits here, seems your query could be simply:

update fact_table
   set clm1 = case
                when clm2 <> '' then clm2
                when clm3 <> '' then clm3
              end
 where (clm2 <> ''
    or  clm3 <> '')
   and  clm1 <> case
                  when clm2 <> '' then clm2
                  when clm3 <> '' then clm3
                end;

But that may be your example that has been over-simplified also.

Hi VVaidar.

Thank you for your reply.
sorry. my query was wrong. The correct query is:

UPDATE
fact_table T1
SET
clm1 = (CASE WHEN T1.clm2 <> '' THEN T2.conv_name1
WHEN T1.clm3 <> '' THEN T3.conv_name1
ELSE T1.clm1
END)
FROM fact_table
LEFT OUTER JOIN conv_table1 T2 ON clm2 = T2.conv1
LEFT OUTER JOIN conv_table2 T3 ON clm3 = T3.conv1
;

There's multiple solutions.
If you really want to keep it as one query, you can try this way but I don't expect great performance (worth the try tho):

 merge into fact_table as tgt
 using (    select t1.pk
                 , case
                     when t1.clm2 <> '' then t2.conv_name1
                     when t1.clm3 <> '' then t3.conv_name1
                     else t1.clm1
                   end as clm1_new
              from fact_table  as t1
         left join conv_table1 as t2 on t2.conv1 = t1.clm2
         left join conv_table2 as t3 on t3.conv1 = t1.clm3
        ) as src
    on tgt.pk = src.pk
  when matched
   and tgt.clm1 <> src.clm1_new
  then update
   set clm1 = src.clm1_new;

Maybe you want to run two updates sequentially:

update fact_table  as t1
   set clm1 = t2.conv_name1
  from conv_table1 as t2
 where t1.clm2  = t2.conv1
   and t1.clm2 <> '';

update fact_table  as t1
   set clm1 = t3.conv_name1
  from conv_table2 as t3
 where t1.clm3  = t3.conv1
   and t1.clm3 <> ''
   and -- specify filter to not update rows updated in the previous statement
;

Either use a merge statement or do two updates. I would choose either one considering query performance. Thank you very much for helping me!