Optimizing individual "insert or update" statements

natalia_2017

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.

marcothesane

Tough luck. You need to use MERGE. SQL is a descriptive language, not a procedural one. And the SQL Server approach of using an "IF" is procedural, and is against the standard. Among other things, it slows down the process massively.
Vertica is a Big Data database, that is for masses of data.

Individual row updates is like trying to get off a train running at 320 km/h to pick up a few nice flowers and jump back in again. Either the train slows down and restarts (and loses massively time), or something breaks ...

I suppose it's not only one row per day that you need to treat this way.
I would collect them in a staging table, and then load them using an Optimized Merge (look that up in the manual)

Oh, you can also program a client with a MERGE statement with parameter markers, and actually trickle feed a merge. But that's programming in C, C++ or Java with embedded SQL.

Good luck
Marco