MySQL retrieving INSERT parameters retrieved by SELECT

Question

If I have table structure as so:

CREATE TABLE a (
  aid INT AUTO_INCREMENT,
  acol1 INT,
  acol2 INT,
  PRIMARY KEY(aid);
)

CREATE TABLE b (
  bid INT AUTO_INCREMENT,
  bcol INT,
  PRIMARY KEY(bid);
)

and run the statement:

`INSERT INTO a SET acol1 = (SELECT MAX(acol1) + 1 as newMax FROM a WHERE id = ?)

Is there anyway for me to retrieve the value of newMax after the query is executed? I am looking for something similar to last_insert_id() in PHP but for temporary values in the query.

Obviously I am trying to not query the database again if possible.

EDIT:

Actual situation:

CREATE TABLE group (
  group_id INT AUTO_INCREMENT,
  PRIMARY KEY(group_id)
) ENGINE = MyISAM;

CREATE TABLE item (
  group_refid INT, --references group.group_id
  group_pos INT, --represents this item's position in its group
  text VARCHAR(4096), --data
  PRIMARY KEY(group_refid, group_pos)
) ENGINE = MyISAM;

So the issue is that when I add a new item to a group, I need to make its

group_pos = MAX(group_pos) WHERE group_refid = ?

which would require a query with something like:

INSERT INTO item (group_refid, group_pos) SET group_refid = 1, group_pos = (SELECT MAX(group_pos) + 1 FROM item WHERE group_refid = 1);

As you know, this query does not work. There is added complexity that there may not be an item entry yet for a particular group_id.

I am trying to get this all into one atomic statement to prevent race conditions.

Answer 1

you cant. insert query is for insering not selecting.

You must run other query like that

   SELECT MAX(acol1) + 1 as newMax FROM a WHERE acol2 = ?

for more read this

Answer 2

I think you can do:

INSERT INTO b
    SET bcol = (SELECT @acol := MAX(acol1) + 1 as newMax FROM a WHERE acol2 = ?);

Then you can use the variable @acol to get the value you want.

EDIT:

Is this what you want?

INSERT INTO item (group_refid, group_pos) 
    SELECT 1, MAX(group_pos) + 1
    FROM item
    WHERE group_refid = 1;

Answer 3

INSERT INTO item (group_refid,group_pos) 
SELECT 1, (
   SELECT IFNULL(MAX(group_pos),0) + 1 
   FROM item 
   WHERE group_refid=1
);

However, if we're talking MyISAM tables explicitly, not another engine, this would work:

mysql> CREATE TABLE items (group_refid INT, group_pos INT AUTO_INCREMENT, PRIMARY KEY(group_refid,group_pos)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO items (group_refid) VALUES (1),(2),(1),(1),(2),(4),(2),(1);
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM items ORDER BY group_refid, group_pos;
+-------------+-----------+
| group_refid | group_pos |
+-------------+-----------+
|           1 |         1 |
|           1 |         2 |
|           1 |         3 |
|           1 |         4 |
|           2 |         1 |
|           2 |         2 |
|           2 |         3 |
|           4 |         1 |
+-------------+-----------+

However, that AUTO_INCREMENT on a second column in the PK is not portable to another database engine.

Answer 4

Not directly in the statement, no. You'll need a separate statement to retrieve values.

But, you could "capture" the value from the SELECT into a user-defined variable, and then retrieve that with a SELECT (in the same database session), if you needed to "know" the value returned from the SELECT.

For example:

INSERT INTO b (bcol) 
SELECT @bcol := (MAX(a.acol1) + 1) AS newMax
  FROM a WHERE a.acol2 = ?)

SELECT @bcol + 0 AS new_bcol

---

NOTE:

Note that the user-defined variable assigned in the select is subject to modification elsewhere in the session, for example, it could be overwritten by the execution of a trigger defined the target table of the INSERT.

As an edge case, not that anyone would do this, but it's also possible there might be a BEFORE INSERT trigger that modifies the value of bcol, before it gets inserted. So, if you need to "know" the value that was actually inserted, that would be available in an AFTER INSERT trigger. You could capture that in a user-defined variable in that trigger.

Running a second, separate query against the a table is subject to a race condition, a small window of opportunity for a another session to insert/update/delete a row in table a, such that it's possible that a second query could return a different value than the first query... it might not be the value that was retrieved the first time. Unless of course you are within the context of an InnoDB transaction with REPEATABLE READ isolation level, or you've implemented some concurrency-killing locking strategy.