> Le 16 mars 2018 à 11:35, Simon Slavin <slav...@bigfraud.org> a écrit :
>
>> for every column I don’t want to change I need to add a sub-select
>> statement. If I need to build this statement dynamically, IMO it would be
>> better to handle this code directly in code:
>>
>> if(record-exists?){
>> UPDATE …
>> } else {
>> INSERT …
>> }
>
> You can use INSERT OR IGNORE for this. First, do an INSERT OR IGNORE command
> which will add a dummy entry, with the right key, if one doesn't already
> exist. Then use an UPDATE command to update the row which now definitely
> exists. If you wrap the two commands in a transaction then even if you get
> power-failure or crash, you will never get a dummy row with no data in the
> database:
>
> BEGIN
> INSERT OR IGNORE <get the key values correct here>
> UPDATE <set the data values here>
> COMMIT
I often do so, slightly differently, passing in all the appropriate values at
the INSERT OR IGNORE stage, and testing its outcome through sqlite3_changes()
to learn if the insert did insert or ignore. Allows to run the update only if
the insert did nothing.
SQLite is an engine in a library and not a black-box-server to which you could
only "talk" through pure SQL, so I always like to think of the database
processing as a collaborative merge of the host code and SQLite features.
The syntax UPDATE table set (X,Y,Z) = (V1,V2,V3) greatly helps in helper layers
to prepare the UPDATE in a syntactic way close to the INSERT INTO table(X,Y,Z)
values(V1,V2,V3). Only have to account for the WHERE clause of the UPDATE.
--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
