Collection of answers to questions about Firebird insert, or, update, PK, exists, record, table. For detailed usage description, take a look at Firebird release notes or README.merge.txt file in docs/sql.extensions directory of your Firebird installation.
Active5 years, 9 months ago
I have the following table of counters:
I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate.
The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.
A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:
EDIT: Thanks for all the great replies. It looks like Paul is right, and there's not a single, portable way of doing this. That's quite surprising to me, as it sounds like a very basic operation.
mu is too short
363k5858 gold badges718718 silver badges693693 bronze badges
Remy BlankRemy Blank
3,48622 gold badges1818 silver badges2323 bronze badges
10 Answers
MySQL (and subsequently SQLite) also support the REPLACE INTO syntax:
This automatically identifies the primary key and finds a matching row to update, inserting a new one if none is found.
andygeersandygeers
4,79799 gold badges4141 silver badges6262 bronze badges
SQLite supports replacing a row if it already exists:
You can shorten this to
This shortcut was added to be compatible with the MySQL Kyle CroninKyle Cronin
REPLACE INTO expression.
61.3k3838 gold badges138138 silver badges156156 bronze badges
I would do something like the following:
Setting the generation value to 0 in code or in the sql but the using the ON DUP.. to increment the value. I think that's the syntax anyway.
jmozjmoz
5,29333 gold badges2525 silver badges2828 bronze badges
the ON DUPLICATE KEY UPDATE clause is the best solution because:REPLACE does a DELETE followed by an INSERT so for an ever so slight period the record is removed creating the ever so slight possibility that a query could come back having skipped that if the page was viewed during the REPLACE query.
I prefer INSERT .. ON DUPLICATE UPDATE .. for that reason.
jmoz's solution is the best:though I prefer the SET syntax to the parentheses
Fire CrowFire Crow
4,64544 gold badges2929 silver badges3333 bronze badges
I don't know that you are going to find a platform-neutral solution.
This is commonly called an 'UPSERT'.
See some related discussions:
Community♦
BradCBradC
34.4k1212 gold badges6161 silver badges8989 bronze badges
In PostgreSQL there is no merge command, and actually writing it is not trivial - there are actually strange edge cases that make the task 'interesting'.
The best (as in: working in the most possible conditions) approach, is to use function - such as one shown in manual (merge_db).
If you don't want to use function, you can usually get away with:
Just remember that it is not fault proof and it will fail eventually.
user80168
Standard SQL provides the MERGE statement for this task. Not all DBMS support the MERGE statement.
Jonathan Leffler
Firebird Sql Update Or Insert Sqlite DatabaseJonathan LefflerFirebird Sql Update Or Insert Sqlite
591k9797 gold badges709709 silver badges10661066 bronze badges
If you don't have a common way to atomically update or insert (e.g., via a transaction) then you can fallback to another locking scheme. A 0-byte file, system mutex, named pipe, etc..
ArnsheaArnshea
10.2k22 gold badges1414 silver badges1919 bronze badges
Firebird Sql Update Or Insert Sqlite Download
Could you use an insert trigger? If it fails, do an update.
Michael ToddMichael Todd
14.7k44 gold badges4444 silver badges6868 bronze badges
If you're OK with using a library that writes the SQL for you, then you can use Upsert (currently Ruby and Python only):
That works across MySQL, Postgres, and SQLite3.
Sqlite Insert Example
It writes a stored procedure or user-defined function (UDF) in MySQL and Postgres. It uses
INSERT OR REPLACE in SQLite3.
C# Sql Insert Or UpdateSeamus AbshereFirebird Sql Update Or Insert Sqlite FileSeamus AbshereInsert Or Update Sql
6,36822 gold badges3535 silver badges5656 bronze badges
Not the answer you're looking for? Browse other questions tagged mysqlsqlsqlitepostgresqlupsert or ask your own question.Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |