is there a way to update a table while joining with itself ,is it possible using MERGE
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
WHEN MATCHED THEN UPDATE SET product_ID = src.pid;
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.