db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Spindler" <bspind...@netuitive.com>
Subject RE: auto generated ids
Date Tue, 13 Oct 2009 15:40:20 GMT
Thanks Knut!

I removed the extra INSERT statements and simply appended the values as
you shown in your example and it worked great.  Thanks for clarifying. 

-----Original Message-----
From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM] 
Sent: Tuesday, October 13, 2009 10:06 AM
To: Derby Discussion
Subject: Re: auto generated ids

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