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](http://stackoverflow.com/questions/5817414/output-clause-in-mysql). — ta.speot.is, Oct 25 '12 at 11:02

VolkerK · Accepted Answer · 2012-10-25T11:42:21.623

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.

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, Oct 08 '19 at 13:35
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`.. — Raymond Nijland, Oct 08 '19 at 13:36

score 0 · Answer 2 · answered Oct 25 '12 at 11:04

0

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

answered Oct 25 '12 at 11:04

Olaf Dietsche

72,253
8
102
198

The behaviour highly depends on the transaction isolation level. Choose the wrong level, and you have a nice race condition. – 0xCAFEBABE Oct 25 '12 at 11:23
@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. – Olaf Dietsche Oct 25 '12 at 11:40
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 Oct 08 '19 at 13:59
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` ... – Raymond Nijland Oct 08 '19 at 14:27

score 0 · Answer 3 · edited Apr 05 '15 at 03:34

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 05 '15 at 03:34

gunr2171

16,104
25
61
88

answered Oct 25 '12 at 11:09

Sivagopal Manpragada

1,554
13
33

if you want inserted values in return write a query to fetch records inserted lastly using same id – Sivagopal Manpragada Oct 25 '12 at 11:11
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. – waxical Oct 25 '12 at 11:17

MySQL: Get a return result from an INSERT SELECT

3 Answers3

Linked