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

MERGE Query

Hi all,
I use this query to marge two tables:

MERGE into tst.tableD  D using tst.tableA A 
ON A.id=D.id AND A.c1_ = D.c1_ --- CONSTRAINT UC_c1 UNIQUE (id,c1_) 
WHEN MATCHED THEN UPDATE SET c2_=A.c2_ ,c3_=A.c3_ 
WHEN NOT MATCHED THEN INSERT (id, c1_, c2_, c3_, C4) VALUES ( A.id, A.c1_, A.c2_, A.c3_, A.C4)

my questions are:
1.I need to marge data from CSV files instead of using table A,I load data to temp table (table A) and then do the
merge query. there is a better solution without the temp table?

2.in table A and table D the is lot of column more then 290 and in the same name ,ther is a better slounig
in the update part instead : SET c2_=A.c2_ ,c3_=A.c3_ ........c289=A.c298,c290=A.c290?

Thank you all!!

Tagged:

Answers

  • Hello,

    You could try making an external table for your csv file, see the documentation here

    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/CreatingExternalTables.htm?tocpath=Administrator's Guide|Working with External Data|_____2

    You could then just copy the csv file you want to merge to the location that’s specified rather than actually importing it into the database. It might be simpler just to do the import though because if you’re not the owner of the database there’s quite a lot of setup involved to make the file location and to get access to it so you can copy your csv files.

    When doing the UPDATE you should make a predicate so that you don’t systematically update every column when you get a match and you change only the ones that are different

    WHEN MATCHED THEN UPDATE SET c2_=A.c2_ ,c3_=A.c3_
    WHERE c2_<>A.c2_ OR c3_=A.c3_ OR …

    When doing the insert you can just do

    WHEN NOT MATCHED THEN INSERT VALUES (A.*)

    If there’s a lot of data use the DIRECT hint

    MERGE /+DIRECT/

    Regards
    Dave

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.