18
I saw the term UPSERT in a blog and would like to better understand the functioning.
- It designates what type of operation?
- In which situations can be used?
- Has to do with idempotency?
2 answers
19
What an upsert is?
It is a command that inserts a new data if there is no one with the same primary key or updates if the key is found, so it never misses because there is no key as in UPDATE or already having the key when making a INSERT.
It designates what type of operation?
It is a normal SQL DML command, in essence joins the UPDATE with the INSERT in a single command.
As a question has arisen, a command is not the same as a keyword. Dbs do not often have a UPSERT is a command that makes an automatic choice between the INSERT and UPDATE. Command is the whole, with several clauses.
In which situations can be used?
Whenever the goal is to put the information in the database and it doesn’t matter if it already exists or not, changing the semantics of what to do according to this existence or not.
It has to do with idempotence?
I don’t know if I understand the context of the use. It’s purely not because every change has the potential to change the end result. It may be that in some context it’s considered that way, but I’m just speculating.
I went to research and talk about the idempotency of the operation as a whole, not that it is, but it helps the result of a written attempt to be. I don’t know if this is correct, I would need to think more. Often people use terms without thinking it through. The arguments I read didn’t convince me initially.
Perhaps the term is being used in the sense that it never returns error (because of nonexistence or duplicity, it may give for other reasons). If you send something that already exists, he’ll do the operation UPDATE and the result of the operation is a hit, and if you send something nonexistent it makes the operation of INSERT and the result of the operation is a hit. At this point is idempotente. But I think "force the bar" to use the term.
3
It is a term used in a database to talk about a command, query or api, which will run a INSERT or a UPDATE, according to a probation.
Examples:
INSERT...ON DUPLICATE KEY UPDATE
INSERT ... ON CONFLICT UPDATE
Browser other questions tagged sql database
You are not signed in. Login or sign up in order to post.
Thanks for the reply Maniero, I read about this relationship with idempotency here in this article, in case it refers to a ticket selling system: http://cloudingmine.com/idempotence-what-is-it-and-why-should-i-care/
– Gaspar