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

   select userid_seq.nextval
   into :new.userid
   from dual;

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? 


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

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

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

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

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

Need Your Help

MySQL Inserting a record for each already existing record

mysql sql

Hi guys basically I have a query that select all the clientId where the flagId = 6

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.