The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to update a table, and only update it?

I need to do many large updates to a large table.  According to the documentation an update is really a delete and insert.  


Is there any way I can stop this behavior, ie have an update be just an update?



  •  I guess not having Vertica be able to do inplace updates is what makes it fast. 

     You need to be aware that Vertica is a write once(on datafile/tablespace -- how ever you wanna call it) and this triggers this bad update performance but in the same time it does make a difference(much faster) when loading and reading from Vertica.

     Is important to know that Vertica was build for a purpouse and is addressing that porpuse. Load data fast and query the data fast.

     I sugesst you find a way to implement merge statement, partition switching, implement partitions.


  • As Adrian has mentioned, Vertica is optimized for load and read operations. Depending on the frequency and amount of data being moved, an alternative is to perform a correlated query and load into a staging table. You can then replace the staged table with your source table.


    If you are looking at possibly using a MERGE operation, consider using a correlated join instead.

  • marcothesanemarcothesane - Select Field - Administrator

    In my experience, the fastest way of updating over 20% of all rows in a big table is as follows:
    Imagine you increase the salary in the target table by 2 percent for everyone (won't we all like that ...?)

    INSERT INTO help_target 
    , name
    , dob
    , salary*1.02
    , hire_dt
    FROM target;
    ALTER TABLE target RENAME TO target_old; -- or drop it if you don't need the previous status any more
    ALTER TABLE help_target RENAME TO target;
    GRANT SELECT ON target TO PUBLIC; -- or whatever grants existed on the old target table

    If you only have to update the last 3 months of a table containing years and years of history, consider partitioning the table by year-month, to only insert -- select the last 3 months's partitions into the help table, and to end up calling the SWAP_PARTITIONS_BETWEEN_TABLES() function.:
    This approach also saves you from having to worry about grants, as you keep the original table.
    Happy playing ...

Leave a Comment

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