Search code examples
sqlderby

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?

Solution

  • 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.