Skip to main content
Question

Optimizing individual "insert or update" statements

  • November 16, 2017
  • 1 reply
  • 9 views
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.

    1 reply

    marcothesane
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • November 16, 2017

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement