The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

How to update column

Dear All,

Could you please advice me how I can update one column on my big table using values column on different table.

For example , I used MERGE JOIN operation but it is not work when I have duplicates

Also, I try to use this constructions :

ALTER TABLE facts.Orders_merge DROP COLUMN AppointedPhone;

ALTER TABLE facts.Orders_merge ADD COLUMN AppointedPhone varchar(64)
DEFAULT (SELECT AppointedPhone FROM facts.Orders_add_column_merge WHERE facts.Orders_add_column_merge.HashOrderId=facts.Orders_merge.HashOrderId);

HashOrderId - It is PK.

I can not drop this table facts.Orders_add_column_merge.

On my case I want to copy "AppointedPhone" from my stage table to big table fastly.

Thanks all in advance.

Comments

  • SruthiASruthiA Employee

    What is the issue you are facing with the method you tried?

  • Jim_KnicelyJim_Knicely Administrator
    edited June 3

    @Testudinateklev

    Are you sure you want a flattened table? Maybe you just want an UPDATE?

    Example:

    UPDATE /*+ DIRECT */
           facts.Orders_merge
       SET AppointedPhone = (SELECT AppointedPhone 
                               FROM facts.Orders_add_column_merge
                              WHERE facts.Orders_merge.HashOrderId = facts.Orders_add_column_merge.HashOrderId);
    
  • @SruthiA We try to update big table

  • @Jim_Knicely we can not use operation update, delete and merge only truncate and insert select * from

  • I found resolve my task via SWAP_PARTITIONS_BETWEEN_TABLES

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.