how can self join be used with Update statement ,have seen people mention merge statement ?

luvpurohit

is there a way to update a table while joining with itself ,is it possible using MERGE

mosheg

With UPDATE no, because UPDATE supports subqueries and joins based on values that are stored in other tables.
The table you specify in the UPDATE list cannot also appear in the FROM list (no self joins).

With MERGE INTO you can.
Here is an example:

MERGE INTO product_dimension tgt
     USING (SELECT (product_key||'.0'||product_version)::numeric(8,2) AS pid, sku_number
     FROM product_dimension) src
     ON tgt.product_key||'.0'||product_version::numeric=src.pid
     WHEN MATCHED THEN UPDATE SET product_ID = src.pid;

See: https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/AdministratorsGuide/Tables/MergeTables/MergeSourceData.htm