Update statement using row_number() 1

[wbodger]

OK, I seem to have come to a wall. I have this query

SELECT cast(study_site_id as varchar(10))+cast([STUDY_PID_SITE_TEXT] as varchar(10)) as sitepid
      ,[SPECIMEN_COLLECTION_SITEIDENTIFIER_TEXT] as b
      ,[PPT_SURG-HX_BIOPSY_DATE] as c
      ,row_number() over(partition by (cast(study_site_id as varchar(10))+cast([STUDY_PID_SITE_TEXT] as varchar(10))),[ppt_surg-hx_biopsy_date] order by [ppt_surg-hx_biopsy_date]) as newid
  FROM [dbEDRN353].[dbo].[tblCore]
  where SPECIMEN_COLLECTION_SITEIDENTIFIER_TEXT is null
order by sitepid

which gives me this

sitepid b       c           newid
4471498	NULL	6/99/1996	1
4471498	NULL	6/99/1996	2
4471498	NULL	6/99/1996	3
4471498	NULL	6/99/1996	4
4471498	NULL	6/99/1996	5
4471498	NULL	6/99/1996	6
4471498	NULL	6/99/1996	7
4471498	NULL	6/99/1996	8
4471498	NULL	9/99/1997	1
4471498	NULL	9/99/1997	2
4471498	NULL	9/99/1997	3
4471498	NULL	9/99/1997	4
4471498	NULL	9/99/1997	5
4471498	NULL	9/99/1997	6
4471498	NULL	9/99/1997	7

what I want to do is simply update b with newid in the underlying table (tblCore). I tried with a cte, but there was no unique record to join tblCore to the cte. Any ideas?

wb

 

[imex]

Hi,

Try:

with CTE_R as
(
  SELECT cast(study_site_id as varchar(10))+cast([STUDY_PID_SITE_TEXT] as varchar(10)) as sitepid
        ,[SPECIMEN_COLLECTION_SITEIDENTIFIER_TEXT] as b
        ,[PPT_SURG-HX_BIOPSY_DATE] as c
        ,row_number() over(partition by (cast(study_site_id as varchar(10)) + 
                                         cast([STUDY_PID_SITE_TEXT] as varchar(10))),
                                        [ppt_surg-hx_biopsy_date] 
                           order by [ppt_surg-hx_biopsy_date]) as newid
  FROM [dbEDRN353].[dbo].[tblCore]
  where SPECIMEN_COLLECTION_SITEIDENTIFIER_TEXT is null
  order by sitepid
)

update CTE_R
set b = newid

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[wbodger]

Ohh... so I can update the CTE and that updates the underlying table. I thought it would treat the cte as a separate object and I couldn't update it. Very cool, I love to learn new stuff, thank you!!