Updating multi billion row table with 200+ fields

Please provide some info about your cluster setup . 
In addition , what is the percentage of  data that need to be update ?    do you have any partition setup on your table ?

If your amounts of records that need to be update is relatively small comparing to the total’s that you have  , I will advise the below method :

·       Insert into temp table all the records that need to be update  with all the case expression you mention , so on target table you will have the updated rows.

·       Delete from source base on the keys that exists on the temp table .

·       Insert from temp to source .

You can run this method in parallel such each thread will work on different population . As I see it will minimize the about of delete vectors  that Vertica will create + will optimize replay delete by TM , please make sure you run purge_table after each iteration otherwise you will performance degradation   on the next iterations .

1. Partition the table that clusters the data to be updated into reasonable chunks. e.g. partition by month
2. Move partition from the source table to a new table using: select move_partitions_to_table('source','month_key','month_key','new_table_00x');
3. Run updates on the single partition.
4. Move the partition back to the primary table: select move_partitions_to_table('new_table_00x','month_key','month_key','source');
5. Drop new table.
6. Iterate with all partitions that need an update.

This is a life saver! Thank you Sajan_Sangraula!!!