Skip to main content

Vertica merge question for doing insert and delete

  • March 29, 2016
  • 1 reply
  • 5 views
knows_not_much
Forum|alt.badge.img+1

I am reading the documentation for the merge command here

 

https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/MERGE.htm

 

 

So in my case there are two conditions for "when not matched". not match can occur if the data is present in the source table and missing from the target table. in this case I do an insert.

 

if the data is missing in the source table but present in the target table, thn I have to do a delete.

 

by reading the documentatino above it seems I can do only one of them.

 

    1 reply

    marcothesane
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • March 30, 2016
    Hi knows_not_much MERGE just adds new information or modifies existing information. It does not delete. UPSERT is a word that people also use for this type of operation. DELETEs are not part of this. From how you describe your requirement: Couldn't you just rename the source to an intermediary name; rename the target to the source name; and rename the intermediary name to the target name, so that what was in the source is now in the target, and what was in the target is now in the source? Cheers - Marco
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement