Click here to Skip to main content
14,770,033 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Expert,

Thanks for your attention.

I have this.

ID Student Class
1  Raj     5
2  Ram     1
3  Ratan   4
4  Raj     6
5  Dijen   3
6  Ratan   7
7  Ritesh  12


Here ID is identity number increasing 1

First occurence of any Student Record.These class data replace with 1.
After updation I want

ID Student Class
1  Raj     1
2  Ram     1
3  Ratan   1
4  Raj     6
5  Dijen   1
6  Ratan   7
7  Ritesh  1


Thanks in advance for your solution.
Posted
Comments
manognya kota 5-Sep-12 5:52am
   
Could you please explain your requirement clearly. Why do you require this?
VIPR@T 5-Sep-12 5:54am
   
Can you please explain in brief? not getting your requirement.
Malli_S 5-Sep-12 5:58am
   
I wonder you may have to write stored procedure (provided you are using SQL :P )
BTW which database you're using ?

try below query:-

update tableA
set class ='1'
where id in (select min(id)
             from tabelA 
             group by name)
   
Comments
manognya kota 5-Sep-12 6:22am
   
5'ed.
ssd_coolguy 5-Sep-12 6:25am
   
Thanks.. :)
Mohamed Mitwalli 5-Sep-12 6:53am
   
5+
Here is a sample approach

CREATE TABLE #Students (ID  INT IDENTITY(1,1), Student VARCHAR(50), Class INT)

INSERT INTO #Students
SELECT 'Raj', 5 UNION ALL
SELECT 'Ram', 1 UNION ALL
SELECT 'Ratan', 4 UNION ALL
SELECT 'Raj', 6  UNION ALL
SELECT 'Dijen', 3 UNION ALL
SELECT 'Ratan', 7 UNION ALL
SELECT 'Ritesh', 12


SELECT * FROM #Students


UPDATE #Students
SET Class = 1
WHERE ID IN
(SELECT MIN(ID) AS ID FROM #Students GROUP BY Student)


SELECT * FROM #Students


DROP TABLE #Students
   
Comments
Mohamed Mitwalli 5-Sep-12 6:54am
   
5+
manognya kota 5-Sep-12 7:27am
   
5'ed
Hi,

As per my understanding, you want to update all the first occurances to 1 in the class column.
If so, refer below link.Also, you need to use a order by clause in your statement to get this work.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102391[^]


Hope this helps.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900