INSERT, and get the auto-incremented value

Question

Consider the following table:

create table language (
    id integer generated always as identity (START WITH 1, INCREMENT BY 1),
    name long varchar,
    constraint language_pk primary key (id)
);

To which I'd insert an entry this way.

insert into language(name) values ('value');

How does one know what value for id was created? Just doing a SELECT using the name field is not valid, because there can be duplicate entries.

In Firebird I know you can use `Returning ID` to have it insert and return the new value for a column. Others should have something similar, though I'm not familiar personally with Derby. — g.d.d.c, Jul 22 '11 at 14:42
http://stackoverflow.com/questions/3131064/get-id-of-last-inserted-record-in-oracle-db http://stackoverflow.com/questions/5576152/getting-id-of-record-just-inserted http://stackoverflow.com/questions/6353343/getting-last-inserted-id http://stackoverflow.com/questions/590927/get-new-sql-record-id Vote to close. — Chris Cudmore, Jul 22 '11 at 14:46

score 6 · Accepted Answer · answered Jul 22 '11 at 14:47

6

Through plain SQL:

 insert into language(name) values ('value');
 SELECT IDENTITY_VAL_LOCAL();

See the manual for details: http://db.apache.org/derby/docs/10.7/ref/rrefidentityvallocal.html

When doing this from a Java class (through JDBC) you can use getGeneratedKeys() after "requesting" them with the approriate executeUpdate() method.

answered Jul 22 '11 at 14:47

Your answer in this http://stackoverflow.com/questions/4894754/retrieve-id-of-record-just-inserted-into-a-java-db-derby-database – deprecated Jul 22 '11 at 16:10

score 1 · Answer 2 · answered Aug 09 '14 at 01:41

1

You use the JDBC method

st.execute(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet keys = st.getGeneratedKeys();

as documented in the Derby manual.

See also Javadocs: DatabaseMetaData#supportsGetGeneratedKeys() and Statement#getGeneratedKeys()

answered Aug 09 '14 at 01:41

eckes

10,103
1
59
71

score 0 · Answer 3 · answered Jul 22 '11 at 14:42

0

You could execute this statement (NB, not 100% sure this syntax is correct for Derby:

SELECT TOP 1 id FROM language ORDER BY id DESC

To find the last inserted ID.

Alternative for Derby:

SELECT MAX(id) from language

Obviously this will only be accurate if no other inserts (including inserts by other users) have happened between your insert and select.

See also this discussion:

answered Jul 22 '11 at 14:42

xan

7,440
8
43
65

4

Not safe in multiuser enviroment - if two users add record at the same time you might get wrong ID back. – ain Jul 22 '11 at 14:45
@ain: Exactly, as I call out in the answer. – xan Jul 22 '11 at 14:46
This will do for my specific program (thank you!) but it's interesting, what @ain says – deprecated Jul 22 '11 at 14:47
@xan Well, you added/edited it after my comment? It wasn't there when I wrote my comment... – ain Jul 22 '11 at 14:52
My comment on other inserts affecting the ID returned to you was there from the start. After reading your comment I added the specific note on other users interacting with the table as it is a very valid point. – xan Jul 22 '11 at 14:55

INSERT, and get the auto-incremented value

3 Answers3

Linked