Counting a cell up per Objects

Question

i got a problem once again :D a little info first: im trying to copy data from one table to an other table(structure is the same). now one cell needs to be incremented, beginns per group at 1 (just like a histroy).

i have this table:

create table My_Test/My_Test2 (
my_Id Number(8,0),
my_Num Number(6,0),
my_Data Varchar2(100));

(my_Id, my_Num is a nested PK)

if i want to insert a new row, i need to check if the value in my_id already exists.
if this is true then i need to use the next my_Num for this Id.

i have this in my Table:

My_Id   My_Num    My_Data
1       1         'test1'
1       2         'test2'
2       1         'test3'

if i add now a row for my_Id 1, the row would look like this: i have this in my Table:

My_Id   My_Num    My_Data
1       3         'test4'

this sounds pretty easy ,now i need to make it in a SQL and on SQL Server i had the same problem and i used this:

Insert Into My_Test (My_Id,My_Num,My_Data)
SELECT my_Id,
  (
    SELECT
      CASE (
          CASE MAX(a.my_Num)
            WHEN NULL
            THEN 0
            Else Max(A.My_Num)
          END) + b.My_Num
        WHEN NULL
        THEN 1
        ELSE (
          CASE MAX(a.My_Num)
            WHEN NULL
            THEN 0
            Else Max(A.My_Num)
          END) + b.My_Num
      END
    From My_Test A
    where my_id = 1
  )
  ,My_Data
From My_Test2 B
where my_id = 1;

this Select gives null back if no Rows are found in the subselect

is there a way so i could use max in the case? and if it give null back it should use 0 or 1?

Edit: Im usung now this:

Insert INTO My_Test  ( My_Id,My_Num,My_Data )
SELECT B.My_Id,
  (
    SELECT COALESCE(MAX(a.My_Num),0) + b.my_Num
    FROM My_Test A
    Where a.My_Id = b.My_Id)
  ,b.My_Data
FROM My_Test2 B
WHERE My_Id = 1

THX to Bharat and OMG Ponies

greets
Auro

Answer 1

Try this one

Insert Into My_Test (My_Id,My_Num,My_Data)
SELECT my_Id,(
    SELECT MAX(NVL(My_Num,0)) + 1     
    From My_Test 
    where my_id = b.my_id
  )
,My_Data
From My_Test2 B
where my_id = <your id>;

Answer 2

Insert Into My_Test (My_Id,My_Num,My_Data) 
select My_id,coalesce(max(My_num),0),'test4' from My_Test
where My_id=1
group by My_id

Answer 3

All solutions have a problem in that they don't work in a multi user environment. If two sessions issue that insert statement at the same time, they would both get the same (my_id,my_num) combination, and one of them will fail with a ORA-00001 unique constraint violation. Therefore, if you need this to work in a multi user environment, the best advice is to use only one primary key column and populate it with a sequence. Keep your my_id column as well, as that is a sort-of-grouping column or foreign key column. If your end users really like to see the "my_num" column in their (web) application, you can use the row_number analytic function.

You can read more about this scenario in this blogpost of mine: http://rwijk.blogspot.com/2008/01/sequence-within-parent.html

Regards, Rob.