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

[David Glass]

How much control over the databases do you have?

If you have some measure of control I'd think the simplest solution 
would be to get away from the auto-incrementing key, and provide your own.
Then you know exactly what the value needs to be in each of your statements.

Barring that, for the three dbs you are dealing with you'll have to 
specialize a bit and use the functions available to each one.

RETURNING for PostgreSQL is probably best, but SELECT LASTVAL() would work.
LAST_INSERT_ID() for MySQL
LAST_INSERT_ROWID() for SQLite

Keeping in mind the danger inherent in those last two (and LASTVAL()), 
of course.

On 11/23/2013, 8:54 PM, Dr. Hawkins wrote:
> 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:
> db:
>
>     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.
> _______________________________________________
> 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
>
>
>
> -- 
> David Glass - Gray Matter Computing
> graymattercomputing.com
> Central Valley: 559-303-4915
> East Bay: 925-335-8486