[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can i fill up this column!

Posted on 2005-04-29
9
Medium Priority
?
212 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:Chadak
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897552
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
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13897736
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
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 13897773
CORRECTION (forgot to include NULL check for *first* row added to table):


UPDATE myTable
SET my_auto_index = CASE WHEN m3.my_auto_index IS NOT NULL THEN m3.my_auto_index
     ELSE (SELECT ISNULL(MAX(my_auto_index), 0) 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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 17

Expert Comment

by:BillAn1
ID: 13900591
hopefully you never need to worry about multiple inserts at once?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13908855
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...
0
 
LVL 1

Author Comment

by:Chadak
ID: 13909628
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


0
 
LVL 17

Expert Comment

by:BillAn1
ID: 13910523
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'
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13910762
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
0
 
LVL 1

Author Comment

by:Chadak
ID: 13911656
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question