Hermann Angstl wrote:
> Matthias Rogel wrote:
>
>
>>you don't want to update, so you don't need to lock a row
>
>
> I dont' watn to update - but i want to have exclusive access to the *last*
> entry in the table (the one with the highest k value).
>
>
>>lock table foo in exclusive mode;
>
>
> This would be an overkill. Explanations follows ...
>
>
>>Oracle noticed that disadvantage and invented sequences.
>
>
> I' aware of sequences - but this is not exactely what i need. My real problem
> is a little bit more complicated. Let's assume we have the following two
> tables:
>
> |--------------|
> | parant_table |
> ----------------
> | parent_name | <- the key
> | ... |
> | |
> |--------------|
> |
> | 1:n
> |
> |--------------|
> | child_table |
> ----------------
> | parent_name | <- the key
> | child_nr | <-
> | ... |
> | |
> |--------------|
>
>
> I want to create unique numbers in table child_table for each parent. The
> numbers have to unique only for each parent_name:
>
> parent_name child_nr
> -----------------------------
> joe 1
> joe 2
> joe 3
>
> john 1
> john 2
>
> ron 1
>
>
How about something like the following (which I haven't tested, but
might give you a starting point):
insert into child_table (parent_name,child_nr,...)
select parent_name,
(
select decode(child_nr,null,1,max(child_nr)+1)
from child_table
where child_table.parent_name(+) = parent_table.parent_name
)
from parent_table
Received on Fri Jan 31 2003 - 12:44:56 CST
