On Fri, Jun 04, 2004 at 11:39:53 +0100,
mike <mike@bristolreccc.co.uk> wrote:
>
> So far I am getting an overall max or no insert.
>
> this is my query
>
> SELECT subj,area, CASE WHEN max(file_no) IS NULL THEN '1' ELSE max
> (file_no)+1 END FROM tb_files GROUP BY (subj||area),subj,area;
You should do a lock table in exclusive mode before doing this, unless
you know nothing else is going on.
An insert would like like:
INSERT INTO table_name (sub, area, file_no) select 'sub_value', 'area_value',
(SELECT max(file_no)+1 FROM table_name where sub = 'sub_value' AND
area = 'area_value')
If you have an index on either area, sub, file_no or sub, area, file_no,
you can rewrite the subselect to use ORDER BY and LIMIT.
It might be better to just assign unique values of file_no for the whole
table. You can use a sequence to support that. That would allow you
to support more concurrency.
