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

[Peter Haworth]

Not sure I understand what you are trying to do here. Are you trying to set
a literal value into the cmd column?  If so, you need single quotes around
it.  If you are trying to execute an INSERT command to get the value of
cmd, you can't do that in an UPDATE command.

What is the intention of the ' || key || ' in the VALUES?  Plus your UPDATE
command names two columns but there is only one value in the VALUES clause.

The best way to do this would be to create a TRIGGER to execute AFTER
INSERT into your remoteTable table which would update the cmd column for
you.  That way, you would only have to issue the INSERT command and the
UPDATE would be handled by the trigger.  You can refer to any values from
the INSERTed remoteTable entry with the syntax new.columname.

Pete
lcSQL Software <http://www.lcsql.com>


On Sat, Nov 23, 2013 at 4:30 PM, Dr. Hawkins <dochawk at gmail.com> wrote:

> 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
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>