Cannot use outer join in update syntax

I would like to update the values ​​in the target table using the values ​​in the right table column of the outer join, but isn't outer join supported?
Please help if you have any solution.

UPDATE
fact_table T1
SET
clm1 = (CASE WHEN T1.clm2 <> '' THEN T2.conv1
WHEN T1.clm3 <> '' THEN T3.conv1
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
;

Tagged:

Best Answer

  • VValdarVValdar Vertica Employee Employee
    Answer ✓

    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
    ;
    

Answers

  • yashiroyashiro Vertica Customer

    Vertica Analytic Database v11.0.1-1

  • VValdarVValdar Vertica Employee Employee
    edited October 2022

    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.

  • yashiroyashiro Vertica Customer

    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
    ;

  • yashiroyashiro Vertica Customer

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

Leave a Comment

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