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

Dr. Hawkins dochawk at gmail.com
Sat Nov 23 23:54:02 EST 2013

Perhaps a simpler way to explain what I'm after:

Suppose I need to retrieve that the value of joe's"stuff" is "fluffy".

Suppose further that when I toss this in, the primary key that gets
assigned will be "5"

So I want to be able to retrieve the command to put this into the :memory:

   INSERT INTO memTable (key, child, stuff) VALUES(5,'Joe', 'fluffy');

but I don't know that 5 will be assigned when I put this into the table.
So my thinking is

  INSERT INTO masterTable (child,scratch) VALUES('Joe','abc123');
  UPDATE masterTable SET cmd='INSERT INTO memTable (key, child, stuff)
VALUES(' || key ||' ,''Joe'', ''fluffy'');' WHERE scratch='abc123';

The actual commands are more complicated; there are about 45 fields that
get set.  There is also no guarantee that the child (Joe) is unique (in
fact, frequently it will not be).

What I'm seeing is that "key" is indeed recognized as a column name--but it
states that there is no such column available.

More information about the Use-livecode mailing list