The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Optimizing individual "update or insert" statements
We are switching to vertica and one of our applications needs to periodically "update or insert" an individual record in a table, i.e if a record with the given keys exists in the table, then update the non-key fields. If the record doesn't exist, insert it.
On SQL Server, we used [update] if \@\@rowcount=0 [insert] construct, but vertica doesn't support it.
Can you please recommend the most optimal way to implement individual "update or insert" on Vertica table? I cannot use "merge", as it only works between two tables which doesn't make sense in case of an individual row update.
0
Comments
Single updates/deletes are not a problem in Vertica. Just make sure you optimize your design for it!
See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AnalyzingData/Optimizations/OptimizingDELETEsAndUPDATEsForPerformance.htm
Example of optimized design:
Sorry, I misunderstood the question. When I first read your post I thought you had a concern with single row updates
Anyway, you can use the MERGE command on a single table. Like this:
@natalia_2017 - You asked the same question twice.
See also:
https://forum.vertica.com/discussion/239186/optimizing-individual-insert-or-update-statements#latest
Thanks a lot for the answer. I was looking at the documentation for 8.0 version in this version merge doesn't support source subqueries. "Source data can come from a base or external table only. Subqueries and joins are not allowed.". However, I checked documentation on Vertica 8.1 and I see that it now supports merging from subquery, which is precisely what I need.
Apologies for the duplicate post - I resubmitted by mistake. How can I delete it? I don't see "delete" option, only "edit"
Glad that worked for you!
There isn't a way to delete posts, but I can ask to have one removed if you want. But since there are responses to each post, it's probably best just to leave them both.