Naveen Nathan wrote:
On a duplicate entry/conflict I would like to retrieve the rowid of the
conflicting entry; by possibly updating columns, or replacing the row
entirely while ensuring the rowid doesn't change.
On an INSERT OR REPLACE it deletes the existing row and inserts the
specified data with the next monotonic number as the rowid.
Unfortunately for me this behaviour is undesirable.
I'm looking for a way to do one of the following:
* Replace the conflicting row while preserving it's rowid then
get the last_insert_rowid().
* Update or 'touch' the conflicting row to retrieve the rowid number,
in hopes that the last_insert_rowid() will be updated..
* Retrieve just the rowid number due to a duplicate conflict.
Naveen,
There is no way to do what you want directly in SQL. You are looking for
an INSERT OR UPDATE type of functionality, this does not exist in SQL.
Your program can retrieve the rowid of a conflicting insert by doing a
select before the insert. If there is a conflicting record then it can
be deleted, and a new record with the same rowid value can then be
inserted. You can wrap this series of SQL statements in a transaction to
make it atomic.
do begin
conflicting_row = do select rowid from t where <conflict condition>
if conflicting_row
do delete from t where rowid = conflicting_row
do insert into t(rowid, ...) values(conflicting_row, ...)
do commit
After this sequence, the correct value will be returned by
last_insert_rowid().
HTH
Dennis Cote
