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

[Dr. Hawkins]

On Sun, Nov 24, 2013 at 10:12 AM, Peter Haworth <pete at lcsql.com> wrote:

> Assuming there really is a column named key, I don't know why you're
> getting the error, maybe because key is a reserved word.
>
> However, as mentioned in my last email, I think David's solution is better
> than my idea of using a trigger.  Your UPDATE statement would be:
>
> UPDATE remoteTable SET cmd='INSERT INTO localTable (key,stuff)
>    VALUES(' || (SELECT last_insert_rowid()) || ',  ''this is some stuff''
> ); ' WHERE
> scratch='scratchKey';
>

What ended up working was

   UPDATE remoteTable SET cmd='INSERT INTO localTable (key,stuff)
      VALUES(' || LASTVAL() || ',  ''this is some stuff''
       ); ' WHERE scratch='scratchKey';

That is, no SELECT to use LASTVAL().

David's suggestion of constructing my own key could work, but looking down
the road, the load on the server and other speed issues will be far faster
having a numeric primary key index than a text one . . .

I also had an interim solution that I didn't particularly like in which the
intitial placeholder kept a temporary value in the string, picked up the id
on the SELECT at the end of the statement, fixed it stuffing to the
:memory: db and marking *that* as hanged, so that the remote master would
be updated on the next pass . . .


-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462