SQL: using the newly inserted index as a value for insertion
pete at lcsql.com
Sun Nov 24 18:29:23 CET 2013
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.
lcSQL Software <http://www.lcsql.com>
On Sat, Nov 23, 2013 at 9:52 PM, David Glass <dglass at graymattercomputing.com
> 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
> 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
> 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:
>> 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
>> 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:
>> David Glass - Gray Matter Computing
>> 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:
More information about the use-livecode