Update Table

Question

Update Table

Bajrang

SSCrazy

Points: 2074
More actions
November 10, 2009 at 8:19 am

#96473

Hi,
I have a table with 2 columns to simplify my problem: As we can see, we have multiple occurrence of same codes in column: Code, I have to populate Subcode column here. For Unique code, I have to put 1 in SubCode Column; So
e.g. Code 1111 should have Subcode 1 for only 1 row;
Code Subcode
1111 0
1111 0
222 0
222 0
222 0
3 0
4 0
5 0
5 0
My Result should look like below:
Code Subcode
1111 1
1111 0
222 1
222 0
222 0
3 1
4 1
5 1
5 0
Thanks in advance.
Regards,
-RP
-RP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can use a CTE that uses the row_number function that is partitioned by code and subcode and then update the table through the CTE where row_number function returns 1. I would have showed you the code, but since you didn’t write a small script to create a table and insert the test data into the table, you’ll have to do with the explanation only.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

I belive this will give you the results you need.

CREATE TABLE #Codes(Code INT,Subcode INT)

INSERT INTO #Codes

SELECT 1111, 0 UNION ALL

SELECT 222, 0 UNION ALL

SELECT 3, 0 UNION ALL

SELECT 4, 0 UNION ALL

SELECT 5, 0 UNION ALL

SELECT 5, 0

SELECT * FROM #Codes

;with numbered as(SELECT rowno=row_number() over

(partition by Code order by Code),Code,Subcode from #Codes)

Update numbered

SET Subcode =1

WHERE Rowno = 1

SELECT * FROM #Codes

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Bajrang SSCrazy Points: 2074 More actions · Answer 3

Thanks a lot bitbucket-25253; that worked like magic. I also tried using NTILE and it worked with that too.

Thanks again.

-RP

-RP