mdnba50 mdnba50 - 2 months ago 10x
MySQL Question

MySQL insert, increment column value per same id

I have this table


ID Name Parent Pos
1 Alex 1 1
2 Mary 1 2
3 John 1 3
4 Doe 2 1
5 Bob 2 2
6 Kate 2 3

Before i do an
query, i select the
id and get the last
position and then increment by 1.

$data = $DB2->query("SELECT * FROM relations
WHERE Parent='1' ORDER BY `Pos` DESC LIMIT 1");

$results = $data->result_array();

foreach($results as $row) {
$pos = $row['Pos']; //last position


$pos = $pos + 1;
$DB2->query("INSERT IGNORE INTO relations (Name,Parent,Pos) VALUES('test',1,'$pos')");

Is there a way i can auto-increment the
column per
column? Or is my current way most efficient?


If you are not worried about race conditions (two inserts happening at the same time), then you can do this in a single statement:

INSERT IGNORE INTO relations (Name, Parent, Pos) 
    SELECT 'test', 1, MAX(pos) + 1
    FROM relations
    WHERE Parent = 1;

However, I would definitely advise you to have a unique index on (Parent, Pos), and to remove the IGNORE -- if an error occurs, you want to know about it.

You can make this safer by using a trigger for the assignment. You could also lock the table so no other inserts can happen during a particular insert. This is an expensive operation and can slow down the system if there is a heavy transaction load.