Cannot use outer join in update syntax
yashiro
Vertica Customer
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
;
0
Best Answer
-
VValdar Vertica Employee Employee
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 ;
0
Answers
Vertica Analytic Database v11.0.1-1
Hi yashiro,
Your joins provides no benefits here, seems your query could be simply:
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
;
Either use a merge statement or do two updates. I would choose either one considering query performance. Thank you very much for helping me!