Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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

Answers

  • moshegmosheg Employee
    edited March 17

    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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.