MySQL: Get a return result from an INSERT SELECT

Question

I have query that INSERTS both explicit values and SELECTd content. I'm also doing basic incrementing.

INSERT INTO `table` (`myID`, `myVal1`, `myVal2`) SELECT `myID` + 1, 'explValHere', 'otherValThere')
FROM `table` ORDER BY `myID` DESC LIMIT 0,1

I am doing this as the table has multiple id's and incrementing within a specific column. So I can't, as you would first say, use auto incrementing and insert_id.

The problem of course is the insert doesn't return the select, but can it? Is there a way of running this insert query and returning any of the result?

SQL Server has an OUTPUT clause that suits this problem perfectly. There's a discussion about emulating that over here. — ta.speot.is
– ta.speot.is, Commented Oct 25, 2012 at 11:02

VolkerK · Accepted Answer · 2012-10-25 11:42:21Z

2

Since your query has a LIMIT 1 you could store the "result" in a session/user-defined variable. Still two queries but reentrant; each connection is its own session.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);

$query = "
    INSERT INTO tbl1 (myID, myVal1, myVal2)
    SELECT @foo:=myID+1, 'val1', 'val2' FROM tbl2 WHERE x=0 LIMIT 1
";
$pdo->exec($query);
foreach( $pdo->query('SELECT @foo as foo') as $row ) {
    echo $row['foo'];
}


function setup($pdo) {
    $pdo->exec('CREATE TEMPORARY TABLE tbl1 (myID int, myVal1 varchar(16), myVal2 varchar(16))');
    $pdo->exec('CREATE TEMPORARY TABLE tbl2 (myID int, x int)');
    $pdo->exec('INSERT INTO tbl2 (myID, x) VALUES (1,1),(2,1),(3,0),(4,0),(5,1)');
}

the "first" record in tbl2 having x=0 is (myID=3,x=0) and the script prints 4.

Other than that and stored procedures et al there's (to my knowledge) nothing like SQLServer's OUTPUT or postgresql's RETURNING clause for MySQL.

edited Oct 25, 2012 at 11:42

answered Oct 25, 2012 at 11:36

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Nijland Over a year ago

SQL language is by definition orderless with table and or resultsets, using LIMIT to match the "first" matching record without ORDER BY is pretty much meaningless.. Pagination or batching in SQL language requires a deterministic ORDER BY to be used meaning there needs to be atleast one column in the ORDER BY clause which has a PRIMARY or UNIQUE key..

Raymond Nijland Over a year ago

So INSERT INTO tbl1 (myID, myVal1, myVal2) SELECT @foo:=myID+1, 'val1', 'val2' FROM tbl2 WHERE x=0 LIMIT 1 should have been INSERT INTO tbl1 (myID, myVal1, myVal2) SELECT @foo:=myID+1, 'val1', 'val2' FROM tbl2 WHERE x=0 ORDER BY id LIMIT 1 assuming id column which has a PRIMARY KEY..

Olaf Dietsche · Accepted Answer · 2012-10-25 11:04:15Z

0

You can (in a transaction) first read the values and afterwards do your insert statement.

answered Oct 25, 2012 at 11:04

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

4 Comments

0xCAFEBABE Over a year ago

The behaviour highly depends on the transaction isolation level. Choose the wrong level, and you have a nice race condition.

Olaf Dietsche Over a year ago

@waxical Yes. I added transaction requirement just to be sure, that no other select/insert comes in between. If you are the only user that's not necessary, of course.

Raymond Nijland Over a year ago

assuming innoDB engine and using SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE before running the topicstarters query should be fine as that should stop phantom reads in the batch -> ORDER BY myID DESC LIMIT 0,1, which @0xCAFEBABE i assume means with his comment -> "The behaviour highly depends on the transaction isolation level. Choose the wrong level, and you have a nice race condition. "

Raymond Nijland Over a year ago

be aware as it dont fully know for sure how SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE is implemented in MySQL a whole table lock might be created for the topicstarters query as there is no limiting WHERE range so all table records could match the SELECT ...

gunr2171 · Accepted Answer · 2015-04-05 03:34:06Z

0

try like this after your normal insert

INSERT INTO `table` (`myID`, `myVal1`, `myVal2`) values ('xxx','xxxx','xxxx');

then execute the queryget last inser id using

$id=mysql_insert_id();

then update the inserted row as below

mysql_query("update table set myid=$id where id=$id");

edited Apr 5, 2015 at 3:34

gunr2171

17.7k26 gold badges68 silver badges102 bronze badges

answered Oct 25, 2012 at 11:09

Sivagopal Manpragada

1,67414 silver badges34 bronze badges

2 Comments

Sivagopal Manpragada Over a year ago

if you want inserted values in return write a query to fetch records inserted lastly using same id

waxical Over a year ago

This relies on having a generated id using auto increment, which I state is not being used on this field. Unless you mean to use an auto-incrementing id to link the two. That's awfully messy with another update.

Collectives™ on Stack Overflow

MySQL: Get a return result from an INSERT SELECT

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related