Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

SQL update query syntax - SQL 2008 R2

Hi experts,

In the data here, SAMPLE_ID_ALT needs to be updated to what I have typed in.  For every 'NS' in the SAMPLE_ID column, the SAMPLE_ID_ALT needs to be set incrementally to NS_001, NS_002 and start again when a new SITE_ID is found.  

I have tried this, and can see why the optimizer might not like it, but I cannot find a workaround.  Can anyone help please?  I would rather not use cursors if I can help it.

UPDATE GB_CB_LITH
SET SAMPLE_ID_ALT = 'NS_'+ RIGHT('000'+CAST(ROW_NUMBER()
                               OVER (PARTITION BY SITE_ID
                                     ORDER BY DEPTH_FROM)
               AS VARCHAR(3)),3)
  WHERE SAMPLE_ID = 'NS'
TEMP-LITH-DATA2.xlsx
0
colinspurs
Asked:
colinspurs
  • 2
1 Solution
 
Surendra NathCommented:
try this out

;WITH C AS
(
SELECT RIGHT('000'+CAST(ROW_NUMBER() OVER (PARTITION BY SITE_ID ORDER BY DEPTH_FROM) AS VARCHAR(3)),3) RN,* FROM GB_CB_LITH WHERE G.SAMPLE_ID = 'NS' 
)

UPDATE G
SET SAMPLE_ID_ALT = 'NS_'+ RN
FROM GB_CB_LITH G
JOIN C 
ON G.<PK> = C.<PK>
WHERE G.SAMPLE_ID = 'NS' 


--NOTE in the above you have to replace the PK with the primary keys

Open in new window

0
 
colinspursAuthor Commented:
Superb!  Thanks.

I think you meant GB_CB_LITH G WHERE G.SAMPLE_ID = 'NS'  in line 3.
                                                  =
0
 
colinspursAuthor Commented:
Brilliant, thanks a lot.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now