SQLite update on select (or vice versa)

Question

Is there a one-statement select-and-update (or update-and-select) method in SQLite?

A trigger can invoke select, but that doesn't allow update to be used in an expression:

CREATE TABLE id ( a integer );
CREATE TRIGGER idTrig AFTER UPDATE ON id BEGIN SELECT old.a FROM id; END;
INSERT INTO id VALUES ( 100 );
INSERT INTO test VALUES ( (UPDATE id SET a=a+1) ); -- syntax error

(Is a triggered select only accessible via the C API?)

I generate object IDs for several databases from a single ID database (with a single row for the next available ID). I'd like to select-and-update on the ID db in one statement, so that concurrent db connections which attach the ID db won't have trouble with this (where two connections could insert before either updates):

INSERT INTO tab VALUES ( (SELECT uuid||oid AS oid FROM id.tab), ... );
UPDATE id.tab SET oid = oid+1;

Answer 1

I'll start with the prerequisite nag: why not use GUIDs? They don't require central authority and are thus more efficient and easier to work with.

If you really need a central ID store, you can make the ID an autoincrement and fetch it with SELECT last_insert_rowid(). If you have to generate your own IDs, then make the ID column a primary key, so you can generate the ID, INSERT it, and retry if the INSERT fails.

Answer 2

This discussion presents two possible solutions:

http://www.mail-archive.com/[email protected]/msg10705.html