Re: insert with select as value

> If your purpose in doing this is just to generate unique keys, you should
> be using sequences instead.

I would need 150 separate sequences, because each idsection needs its
own, independent sequence of idthread.

> Note that you probably want to lock the table before doing this or
> two transactions running at the same time can generate the same
> value for idthread.

That's a surprise. I could have made two separate queries (a select and
then insert) in my programming language (Python), but I wanted to make
it in one query PRECISELY because I thought that would prevent the race
condition that you describe. Are you quite sure?

> For example:
> SELECT coallesce(max(idthread),0)+1 FROM table WHERE idsection = 'CZE';

Someone already sent me this by private email, and it works fine.

> If there is a compound index on idthread and idsection, then you are probably
> better off using something like the following to take advantage of the index:
> coallesce((SELECT idthread FROM table WHERE idsection = 'CZE' ORDER BY
> idthread DESC, idsection DESC LIMT 1))+1

That's interesting and valuable, thank you very much.


--
Milos Prudek
_________________
Most websites are
confused chintzy gaudy conflicting tacky unpleasant... unusable.
Learn how usable YOUR website is! http://www.spoxdesign.com