Vertica merge question for doing insert and delete

knows_not_much

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.

 

marcothesane

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