Help with Update Query

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 1, 2010 at 2:24 am

  #1230079

  You don't need to join the CTE back to the source table, you can update it directly:

  DROP TABLE #TABLE_1

  CREATE TABLE #TABLE_1 (S_NO INT IDENTITY,PRODUCT_NAME VARCHAR(10),PRODUCT_ID INT, COUNT_1 INT DEFAULT 0 )

  INSERT INTO #TABLE_1 (PRODUCT_NAME,PRODUCT_ID)

  SELECT 'SOAP',10001 UNION ALL

  SELECT 'SOAP',10001 UNION ALL

  SELECT 'SOAP',10001 UNION ALL

  SELECT 'RUM',10002 UNION ALL

  SELECT 'RUM',10002 UNION ALL

  SELECT 'RUM',10002 UNION ALL

  SELECT 'RUM',10002 UNION ALL

  SELECT 'WHISKY',10003

  ;WITH UpdatableCTE AS (

  SELECT PRODUCT_NAME, PRODUCT_ID, COUNT_1,

  [COUNT] = ROW_NUMBER() OVER (PARTITION BY PRODUCT_NAME, PRODUCT_ID ORDER BY PRODUCT_NAME, PRODUCT_ID, S_NO)

  FROM #TABLE_1)

  UPDATE UpdatableCTE SET COUNT_1 = [COUNT]

  SELECT * FROM #TABLE_1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden