db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: have identity_val_local(), want table.curr_id
Date Thu, 16 Nov 2006 15:05:20 GMT
Keith Irwin wrote:
> Folks---
> 
> I want to run something similar in a single transaction (just a loop 
> that runs each query one after another then commits):
> 
> Assuming table (shorthand):
>   order (order_id autoincrement, name)
>   items (item_id autoincrement, order_id, name)
> 
> And queries:
>   insert into orders (name) values ('foo');
>   insert into items (order_id, other) values (identity_val_local(), 'bar');
> 
> This works fine.   It picks up the order_id inserted automatically (via 
> autoincrement) on the order table and uses it in the items table foreign 
> key.
> 
> However, when I do the following:
> 
>   insert into orders (name) values ('foo');
>   insert into items (order_id, other) values (identity_val_local(), 'bar');
>   insert into items (order_id, other) values (identity_val_local(), 
> 'quux');
> 
> I get a constraint error because the second identity_val_local() call 
> refers to the recent item table autoincrement, and not the order 
> autoincremented key (which makes sense reading the docs).

> Is there some way to do the above without having to use:

Can you use a single INSERT statement for the items?

insert into items (order_id, other) values
       (identity_val_local(), 'bar'),
       (identity_val_local(), 'quux');

Dan.




Mime
View raw message