3 ms·
Yes, but did you just do an update and an insert every time, or did you have some code that checked the return values and not run the extra unnecessary command?
by jedberg 9y ago
Yes, but did you just do an update and an insert every time, or did you have some code that checked the return values and not run the extra unnecessary command?
- cobythedog 9y agoThey are run together every time. You could run a check but it would probably cost as much as the update, which does nothing when a record is not found. In PostgreSQL (for example) you could use a CTE and the RETURNING clause to prevent two statements from having to run. I don't think sqlite supports that. Current version of PostgreSQL makes that unnecessary as it has "upsert" as well.
- jedberg 9y agoYou are right. You've chosen to trade off network round trips for extra work in the database and slightly longer row locks while both commands run, which seems like a fair tradeoff.
- kodablah 9y ago> You could run a check but it would probably cost as much as the update, which does nothing when a record is not found. True if the generation of the insert doesn't have some heavier burdens on the caller side. Otherwise, start a txn, do the first, check if 0 updated, then do the second.
- merb 9y agowell even if you have an if after your update it's not what he basically meant. What he meant is that some people would've written the if/else with SQL and not with the language they written ?