CODEWITHSUNDEEP
mysqlinsert
Ivan
Ivan

Reputation: 15932

MySql: Insert a row and get the content

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.

Upvotes: 33

Views: 37598

Answers (5)

alionthego
alionthego

Reputation: 9773

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.

Upvotes: 1

Jon Black
Jon Black

Reputation: 16559

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');

Upvotes: 11

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

Execute your insert statement and then you can do this:

SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID()

Upvotes: 17

Gaurav
Gaurav

Reputation: 28755

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425593

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.

Upvotes: 14

Related Questions