29

Is it possible to insert a row and get the values inserted in the same query?

Something like...

INSERT INTO `items` (`item`, `number`, `state`) 
(SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3')

And then, get ID and execute a

SELECT * FROM `items` WHERE `id`='$id'

But using only one query.

Ivan
  • 14,692
  • 17
  • 59
  • 96

5 Answers5

13

Execute your insert statement and then you can do this:

SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID()
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • @Quassnoi: Sorry if I wasn't clear. The expectation is that the OP executes the INSERT statement as given in the original question. I'll flesh out the answer better. – Joe Stefanelli Mar 25 '11 at 13:20
  • the original question asks for "insert a row and get the values inserted in the same query". – Quassnoi Mar 25 '11 at 13:25
  • @Quassnoi: Which we both know cannot be done here. Hence I'm giving OP an alternative solution. – Joe Stefanelli Mar 25 '11 at 13:28
11

you can call a stored procedure which will perform the insert and return a resultset in a single call from your app layer to mysql:

Stored procedure call

mysql> call insert_user('bar');
+---------+----------+
| user_id | username |
+---------+----------+
|       1 | bar      |
+---------+----------+
1 row in set (0.02 sec)

$sqlCmd = sprintf("call insert_user('%s')", ...);

Simple example:

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;


drop procedure if exists insert_user;

delimiter #

create procedure insert_user
(
in p_username varchar(32)
)
begin
declare v_user_id int unsigned default 0;

 insert into users (username) values (p_username);

 set v_user_id = last_insert_id();

 -- do more stuff with v_user_id e.g. logs etc...

 select * from users where user_id = v_user_id;

end#

delimiter ;

call insert_user('bar');
Jon Black
  • 16,223
  • 5
  • 43
  • 42
9

No, it's not possible in MySQL (unlike PostgreSQL, SQL Server and PL/SQL in Oracle).

You will have to do it in separate queries.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
1

You can do this using multiple statements if you like to choose that route. Firstly when you connect to your database make sure that multiple statements is set to true:

var connection = mysql.createConnection({
    host: databaseHost,
    user: databaseUser,
    password: databasePassword,
    database: databaseName,
    multipleStatements: true
});

Then you can just define your sql as:

var sql = "your insert statement; your select statement";

Just separate individual statements using the semi colon. Your select result will be results[1] in this example.

alionthego
  • 8,508
  • 9
  • 52
  • 125
0

if you are using php then

instead of this you can use

mysql_insert_id();

which will give the id of last inserted record.

The other data will be same as inserted. Only id will change which you can get by mysql_insert_id()

So you do not need to run second query.

Gaurav
  • 28,447
  • 8
  • 50
  • 80