db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: auto generated ids
Date Tue, 13 Oct 2009 14:05:55 GMT
Brian Spindler <bspindler@netuitive.com> writes:

> I have a table let's say FOO with a column name 'ID' and it is auto
> increment.  
>
> Now I want to insert one row into that table and then insert 10 rows
> into an adjacency table with the new generated id.  I thought something
> like this might work: 
>
> INSERT INTO FOO(NAME) VALUES ('BAR');
> INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
>   VALUES (
> 		(SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
> 'MyName');
> ... 
>
> and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads the
> generated id from ADJTBL as opposed to the FOO table.  What am I doing
> wrong?  Is this possible?  I am running this from a .sql file within IJ
> tool.

IDENTITY_VAL_LOCAL() returns the most recently assigned value of an
identity column for a connection in a single-row INSERT statement, so
once you've inserted a new row into ADJTBL, the return value from
IDENTITY_VAL_LOCAL() will change. See
http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html.

Inserting all 10 rows into ADJTBL in one INSERT statement should work,
though:

INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME) VALUES
  (IDENTITY_VAL_LOCAL(), 'MyName1'),
  (IDENTITY_VAL_LOCAL(), 'MyName2'),
  ...
  (IDENTITY_VAL_LOCAL(), 'MyName10');

-- 
Knut Anders

Mime
View raw message