How to specify 'first next unique value' in a query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bram
Forum Newbie
Posts: 8
Joined: Sat Feb 19, 2011 11:45 am

How to specify 'first next unique value' in a query?

Post by Bram »

Got a table where each row has a unique 'id', and a 'rank' which represents some sort of ordering or priority. Both are integers.

I want to insert a new row, getting the first next unique rank available. Or update an existing row, changing its rank into the first next unique available.

This works most of the time: [sql]SELECT MAX(rank)+1 FROM funkyTable; # I assign this query's result to $nextRank

INSERT INTO funkyTable (rank) VALUES ($nextRank)
# or:
UPDATE funkyTable SET rank=$nextRank WHERE id=$someId[/sql]
However, this obviously isn't "thread safe". If multiple instances of this script are running simultaneously (which happens to be the case in my situation), another instance may set another row's rank to '$nextRank' just between the select and insert/update queries above.

I don't know if MySQL queries are atomic, but I guess something like this would be safe: [sql]INSERT INTO funkyTable (rank) VALUES ((SELECT MAX(rank)+1 FROM funkyTable))
# or:
UPDATE funkyTable SET rank=(SELECT MAX(rank)+1 FROM funkyTable) WHERE id=$someId[/sql]
But this gives an error:
You can't specify target table 'funkyTable' for update in FROM clause

What's the correct way of doing this?

(Notice there is no Auto Increment on the 'rank' field, there's already Auto Increment on 'id' which is the primary key)
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: How to specify 'first next unique value' in a query?

Post by twinedev »

You are going to need to do this as multiple queries as SQL will not let you update any value that is being used.

I haven't tried it myself, but you may want to look at running a transaction, which I believe will lock the table to execute the two transactions then release it. I could be wrong, it has bee 10 years since I learned about them, never had practical need for them.

If I had spare time, I'd play around with it myself (I learn lots of things by trying to figure out solutions for people here, that is why I love it here, pushed my limits ;-)

-Greg
Post Reply