Getting the last value from a sequence in SQL and insert it into another table (Oracle)

I have 2 tables, which were created by executing the following (keep in mind that every query is executed through on node.js, i.e., connection.execute('select....') ):

create table User(userid int not null, name varchar(50) not null, primary key(userid));
create table Random(userid int not null, random int, primary key (userid), foreign key (userid) references User(userid) );

I also added a sequence to auto increment the userid:

create sequence userid_seq;

create or replace trigger userid_bir
before insert on User
for each row

begin 
   select userid_seq.nextval
   into :new.userid
   from dual;
end;
/

Now, I have a process whereby after I insert a new user, that user's id is inserted immediately into Random. I browsed through Stackoverflow and came up with:

insert into User(name) values('John');
SET @last_id = LAST_INSERT_ID();
insert into Random(userid, random) values( last_id, 2);

However, I got the following error:

SP2-0735: unknown SET option beginning "@last_id"

Any ideas?

My other concern is that if there are 2 computers trying to insert 2 users (in total) at the same time, the last_id value (or MAX userid value) might mess up. That is, the server might be executing:

insert into User(name) values('John'); ## from Person A
insert into User(name) values('Brian'); ## from Person B
SET @last_id = LAST_INSERT_ID(); ## from Person A, say it's set to 1
SET @last_id = LAST_INSERT_ID(); ## from Person B, say it's set to 2
insert into Random(userid, random) values( last_id, 2); ## from Person A
insert into Random(userid, random) values( last_id, 2); ## from Person B, error: duplicate values? 

Answers


That's probably MySQL syntax. In Oracle you can use RETURNING INTO clause.

declare
    last_id number;
begin
    insert into user(name) values('Abcd') 
    returning userid into last_id;

    insert into Random(userid, random) values( last_id, 2);
    commit;
end;
/

userid_seq.currval will return the most recent value returned for the sequence in the current session. So you can

INSERT INTO random( userid, random )
  VALUES( user_id_seq.currval, 2 );

If you want to have the value in a local variable

DECLARE
  l_userid user.user_id%type;
BEGIN
  INSERT INTO user(name)
    VALUES( 'John' )
    RETURNING userid 
         INTO l_userid;
  INSERT INTO random( userid, random )
    VALUES( l_userid, 2 );
END;

Need Your Help

Using variables from imported file giving a NameError, even though 'global' has been used

python variables import nameerror

I am designing a program that simulates a Turing machine, with the rules in a separate file for easy editing. Unfortunately, it throws up a NameError when it first tries to compute from the ruleTab...

How can I use annotate() to count a subset of related models in Django?

django django-models annotate

I'm trying to use Django's annotate feature to add the count of a related model to a queryset. However, I don't want a full count of related objects, I only want to count the active ones (i.e., "