SQL: using the newly inserted index as a value for insertion
dochawk at gmail.com
Sat Nov 23 19:30:00 EST 2013
I use an SQLite :memory: database, and store insertion commands into the
"remote" database (remote postgres, disk SQLite, etc.)
To deal with newly created data, I have something like
INSERT INTO remoteTable (scratch) VALUES (scratchKey);
UPDATE remoteTable SET cmd=INSERT INTO localTable (key,cmd)
VALUES(' || key || ' ) WHERE scratch=scratchKey;
However, it tells me that "column key does not exist"--where key is the
autoincrementing primary key for the table!
It's two commands as much because I assumed that key would get a temporary
value pending commit.
I'm trying to avoid two transactions due to the lag over the connections
(from here, I have a half-second penalty each time I hit the db).
Is there a way to do this that's portable across sqlite, postgres, & mysql?
Dr. Richard E. Hawkins, Esq.
More information about the Use-livecode