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

[Peter Haworth]

I think David has a better solutionthan my trigger idea..  You'd have to
replace the word "key" with (SELECT last_insert_rowid()) and be sure that
everything is done within the scope of a single transaction.  That assumes
that you have an INTEGER PRIMARY KEY column in your table since rowid is a
synonym for it.

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


On Sat, Nov 23, 2013 at 9:52 PM, David Glass <dglass at graymattercomputing.com
> wrote:

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