CODEWITHSUNDEEP
sqlmysqlinsert
Ankur
Ankur

Reputation: 51100

SQL: is it possible to combine an INSERT and SELECT statement into one

I want to add a row using SQLs INSERT statement. Is it possible that as part of this statement I can somehow get the value of the column userId which I don't update but is the AUTO_INCREMENT primary key. I need this value to update another table, however I can't follow the Insert statement immediately with a SELECT statement as there is no other unique identifier in the table on which to select.

INSERT INTO objectUrl(disp_name, loggedIn) VALUES('please change this', true)

Is it possible to get the row number (column name userId) and if so how do you do it?

Upvotes: 0

Views: 798

Answers (2)

colithium
colithium

Reputation: 10327

In MySQL it's called LAST_INSERT_ID(). I believe to be technically correct, the two statements should be wrapped in a transaction so that some other INSERT doesn't mess up what ID you get back.

In SQL Sever you have IDENT_CURRENT(‘tablename’) which will only grab it from that table (still need a transaction to be safe). You could also use SCOPE_IDENTITY() which theoretically will always return the one you expect as long as you aren't doing something weird with your connection.

Upvotes: 3

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

For MySQL you have:

select last_insert_id()

Upvotes: 2

Related Questions