SQL: using the newly inserted index as a value for insertion

Dr. Hawkins 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.
(702) 508-8462

More information about the Use-livecode mailing list