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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
Btw, it is intended to be trigger code:
CREATE TRIGGER ...
ON ...
AFTER INSERT
AS
UPDATE myTable
...same as above...
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
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'
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
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),
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
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
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
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