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

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  November 10, 2009 at 8:30 am

  #1077310

  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

  November 10, 2009 at 8:39 am

  #1077320

  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 1111, 0 UNION ALL

  SELECT 222, 0 UNION ALL

  SELECT 222, 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

  November 10, 2009 at 9:03 am

  #1077329

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

  Thanks again.

  -RP

  -RP