Chernogorskiy Fedor Chernogorskiy Fedor - 3 months ago 6
SQL Question

Using ID of the last inserted row in a statement (Derby)

i'm trying to get the ID of the last inserted record to use it in a further statement. The initial insert query is like this:

insert into app.customer(cust_name, pass) values ('tester', '123123');


But when i use IDENTITY_VAL_LOCAL():

select IDENTITY_VAL_LOCAL() from app.customer;


for some reason i get 9 duplicate rows as a result, which is confusing.
I need the ID of the added customer so i can use it in a new query right after that.

insert into app.coupon_customer(coup_id, cust_id) values(coupon_id_value, customer_id_value)


Is it possible to combine the insert query with the IDENTITY_VAL_LOCAL() function?

Answer

Not a derpy expert but can't you do:

insert into app.coupon_customer(coup_id, cust_id) 
values(coupon_id_value, IDENTITY_VAL_LOCAL())

If you do:

select IDENTITY_VAL_LOCAL() from app.customer;

you are just selecting the last inserted id FOR EACH row in app.customer. So yes that gives you 9 duplicate rows if app.customer contains 9 rows.

Comments