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

Dr. Hawkins dochawk at gmail.com
Sat Nov 23 20:43:08 EST 2013

On Sat, Nov 23, 2013 at 5:01 PM, Peter Haworth <pete at lcsql.com> wrote:

> 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.

The cmd is used to re-create the data in the in-memory db (this let me go
to 1 remote table per debtor, rather than 3 or more).  The catch is that as
I write the command, and send it to the remote, I don't know what value
will be assigned.

If I use postgres' "returning" extension, I can get the index of the new
entry (but aside from non-portability, the transaction would be over, and I
can't get multiple queries back, and . . . .).

I'm trying to use the column name of the unique key to put the value of the
key into the middle of a string in the same record.


    INSERT INTO . . .
      <creates a record with a key and a scratch-field that I can find >
      <the unique key now exists, at least for the rest of the transaction >
    UPDATE . . . SET cmd="blah-blah" WHERE {scratch matches}
        <tries to use the key created in insert, and stick it into the
        < of the blah-blah tring>

What is the intention of the ' || key || ' in the VALUES?

concatenation.  Something got eaten . . .  That *should* have been,

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

so that a query would produce a cmd of
  INSERT INTO localTable (key,stuff)
      VALUES(<the assigned key here>,  'this is some stuff' ) ;

> Plus your UPDATE
> command names two columns but there is only one value in the VALUES clause.
(same glitch)

> 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.

That could be an ugly (and expensive?) replacement, couldn't it--I'd be
trying to match a "magic string" I left in the middle of a string with an

Or am I missing something?

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

More information about the Use-livecode mailing list