Link to home
Start Free TrialLog in
Avatar of Chadak
Chadak

asked on

How can i fill up this column!

Hi all
I have this Table
my_id INTEGER
my_Index VARCHAR(255)
my_auto_Index INTEGER  
CONSTRAINT my_Unique_stuff UNIQUE (my_id, my_Index)

The value in the column "my_auto_Index" will be generated automaticly.  See exemple below for the rules and explanation
my_id   my_Index   my_auto_Index
1          aaa            1  <-- its going to be 1 its the first time aaa appear in "my_Index" column
1          bbb            2  <-- its going to be 2 its the first time bbb appear in "my_Index" column, but it's the second different value
2          aaa            1  <-- It's going to be 1 since this value already existed in "my_Index" column and it was the first value
2          ccc             3  <-- first time ccc appear and its 3, because its the third different value to appear in the column my_Index
2          bbb            2  <-- and so on
3          ddd            4  <-- it's 4, ddd is the fourth different value in column "my_Index"
Don't know if this is clear enough.
Now what im wondering , how can i do this, and whats the best way to do this. i was thinking maybe a trigger could do this.

Thx
Chad

Avatar of rafrancisco
rafrancisco

An insert trigger will be a good idea:

UPDATE A
SET My_Auto_Index = C.My_Auto_Index + 1
FROM YourTable A INNER JOIN Inserted B
   on A.My_ID = B.My_Id AND A.My_Index = B.My_Index
INNER JOIN (SELECT My_Id, MAX(My_Auto_index) as My_Auto_Index FROM YourTable GROUP BY My_ID) C
   ON A.My_ID = C.MyID
Avatar of Scott Pletcher
I think this is it, but haven't tested it yet ... will do so ASAP:


UPDATE myTable
SET my_auto_index = CASE WHEN m3.my_auto_index IS NOT NULL THEN m3.my_auto_index
      ELSE (SELECT MAX(my_auto_index) FROM myTable) + 1 END
FROM myTable
INNER JOIN inserted i ON i.my_id = myTable.my_id AND i.my_Index = myTable.my_Index
LEFT OUTER JOIN (
      SELECT my_index, MIN(my_auto_index) AS my_auto_index
      FROM myTable m2
      GROUP BY my_index
) AS m3 ON m3.my_index = myTable.my_index
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hopefully you never need to worry about multiple inserts at once?
Why's that?  My code worked for me when I did multiple INSERTs at one time.


Btw, it is intended to be trigger code:

CREATE TRIGGER ...
ON ...
AFTER INSERT
AS
UPDATE myTable
...same as above...
Avatar of Chadak

ASKER

Hi!
  Sorry for the late reply, it was the week-end :)
For now i can't test any of the anwser, i'm not able to create any trigger at all, ( must be a mistake on my part ) even the most trivial one. As soon as i got this figure, i'll let you kow


Scott,
if you insert multiple rows with different, new, values for my_index they will all be assigned the same new value for my_auto_index, rather then each new value of my_index getting a new value. Probably not a real-world issue, so no need to code for it, hence i'm not offering a solution, just pointing out a theoretical limitation.
e.g. try this :
insert into myTable (my_id, my_index)
select 4,'eee'
union
select 5,'eee'
union
select 6,'fff'
union
select 7,'fff'
D'OH!  OK, how about this, then? :-) :


ALTER TRIGGER my_trigger
ON myTable
AFTER INSERT
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @max_my_auto_index INT
SET @max_my_auto_index = (SELECT ISNULL(MAX(my_auto_index), 0) FROM myTable)
DECLARE @my_auto_indexes TABLE (
      my_index VARCHAR(40),
      my_auto_index INT      
)
INSERT INTO @my_auto_indexes
SELECT DISTINCT i.my_index, m.my_auto_index
FROM inserted i
LEFT OUTER JOIN (
     SELECT my_index, MIN(my_auto_index) AS my_auto_index
     FROM myTable
     GROUP BY my_index
) AS m ON m.my_index = i.my_index

UPDATE @my_auto_indexes
SET @max_my_auto_index = @max_my_auto_index + 1,
      my_auto_index = @max_my_auto_index
WHERE my_auto_index IS NULL

UPDATE myTable
SET my_auto_index = mai.my_auto_index
FROM myTable mt
INNER JOIN @my_auto_indexes mai ON mai.my_index = mt.my_index
Avatar of Chadak

ASKER

OK, works like a charm.
Thx all for those fast answer
My Error was, i had changed my connection to a access database, and this command dosnt seem to work on access(didnt figure it out yet, but its working on sqlserver). As soon as i switched on sql server it worked.
Chad