What is an upsert?

  

What is an upsert?

It is a command that inserts a new data if there is no one with the same primary key or if the key is found, so it never gives error because the key does not exist as it does in UPDATE or because it already has the key when makes a INSERT .

  

Does it designate what kind of operation?

It is a normal SQL DML command, in essence it joins the UPDATE with the INSERT in a single command.

As you have generated a question, a command is not the same as a keyword. DBs do not usually have a UPSERT , but a command that makes an automatic choice between INSERT and UPDATE . Command is the whole, with several clauses.

  

In what situations can it be used?

Whenever the purpose is to put the information in the database and it does not matter whether it already exists or not, changing the semantics of what to do as it exists or not.

  

Does it have to do with idempotency?

I do not know if I understand the context of use. Purely it does not have to see because every change has potential to change the final result. It may be that in some context this is considered, but I'm just speculating.

I've been researching and talking about the idempotency of the operation as a whole, not that it is, but it helps the result of an attempt to write. I do not know if this is correct, I would need to reflect more. Often people use terms without thinking well. The arguments I read did not convince me initially.

Perhaps the term is being used in the sense that it never returns error (because of non-existence or duplicity, it may give for other reasons). If you send something that already exists it does the UPDATE operation and the result of the operation is a hit, and if you send something non-existent it does the INSERT operation and the result of the operation is a hit. At that point it is idempotent. But I think "force the bar" to use the term.

This is a database term for a command, query, or api, which will execute a INSERT or a UPDATE , according to a conditional.

Examples:

  

INSERT...ON DUPLICATE KEY UPDATE

     

INSERT ... ON CONFLICT UPDATE