Solved

SQL Table Update with Row Number

Posted on 2014-01-15
6
862 Views
Last Modified: 2014-01-16
Hello Experts,

I am running the following query that returns the following result set

select * from pas_todays_assessment_ where person_id = '95897949-041E-4943-9E2B-EDAA50BB504E' and enc_id = 'BA530BE4-5D34-47B4-848F-0E5D61CAE212'
order by create_timestamp desc

Open in new window


enterprise_id practice_id person_id                            created_by  create_timestamp        create_timestamp_tz modified_by modify_timestamp        modify_timestamp_tz row_timestamp      enc_id                               seq_no                               txt_dx_code          txt_dx_descp                   txt_dx_status txt_dx_prior
------------- ----------- ------------------------------------ ----------- ----------------------- ------------------- ----------- ----------------------- ------------------- ------------------ ------------------------------------ ------------------------------------ -------------------- ------------------------------ ------------- ------------
00001         0001        95897949-041E-4943-9E2B-EDAA50BB504E 0           2014-01-14 19:19:52.293 0                   0           2014-01-14 19:19:52.293 0                   0x000000000D3A300C BA530BE4-5D34-47B4-848F-0E5D61CAE212 D0A9B515-5153-471C-9A4A-3E4FFC0484C0 585.3                CKD Stage III                  NULL          NULL
00001         0001        95897949-041E-4943-9E2B-EDAA50BB504E 0           2014-01-14 19:19:52.243 0                   0           2014-01-14 19:19:52.243 0                   0x000000000D3A300A BA530BE4-5D34-47B4-848F-0E5D61CAE212 61441FEC-7D31-4DBC-A202-9A3756011816 V49.75               Below Knee Amputation          NULL          NULL
00001         0001        95897949-041E-4943-9E2B-EDAA50BB504E 0           2014-01-14 19:19:52.190 0                   0           2014-01-14 19:19:52.190 0                   0x000000000D3A3008 BA530BE4-5D34-47B4-848F-0E5D61CAE212 39D41C76-017D-40CE-8140-5269CFA6F49B 716.90               Arthritis                      NULL          NULL

Open in new window


I am basically looking to develop a procedure that will update the txt_dx_prior column with the row number based on the results of the above query. So i am looking to update the table to look something like this.

enterprise_id practice_id person_id                            created_by  create_timestamp        create_timestamp_tz modified_by modify_timestamp        modify_timestamp_tz row_timestamp      enc_id                               seq_no                               txt_dx_code          txt_dx_descp                   txt_dx_status txt_dx_prior
------------- ----------- ------------------------------------ ----------- ----------------------- ------------------- ----------- ----------------------- ------------------- ------------------ ------------------------------------ ------------------------------------ -------------------- ------------------------------ ------------- ------------
00001         0001        95897949-041E-4943-9E2B-EDAA50BB504E 0           2014-01-14 19:19:52.293 0                   0           2014-01-14 19:19:52.293 0                   0x000000000D3A300C BA530BE4-5D34-47B4-848F-0E5D61CAE212 D0A9B515-5153-471C-9A4A-3E4FFC0484C0 585.3                CKD Stage III                  NULL          1
00001         0001        95897949-041E-4943-9E2B-EDAA50BB504E 0           2014-01-14 19:19:52.243 0                   0           2014-01-14 19:19:52.243 0                   0x000000000D3A300A BA530BE4-5D34-47B4-848F-0E5D61CAE212 61441FEC-7D31-4DBC-A202-9A3756011816 V49.75               Below Knee Amputation          NULL          2
00001         0001        95897949-041E-4943-9E2B-EDAA50BB504E 0           2014-01-14 19:19:52.190 0                   0           2014-01-14 19:19:52.190 0                   0x000000000D3A3008 BA530BE4-5D34-47B4-848F-0E5D61CAE212 39D41C76-017D-40CE-8140-5269CFA6F49B 716.90               Arthritis                      NULL          3

Open in new window


Would a script similar to the one here be what i am looking for?

WITH CTE AS 
(SELECT *, ROW_NUMBER() OVER (ORDER BY create_timestamp) Rn
FROM pas_todays_assessment_)

UPDATE pta
SET pta.txt_dx_prior = tbl.Rn
FROM pas_todays_assessment_ pta JOIN CTE tbl 
ON pta.seq_no = tbl.seq_no
WHERE pta.RowNumber IS NULL

Open in new window


Any help would be greatly appreciated.
0
Comment
Question by:robthomas09
6 Comments
 
LVL 13

Expert Comment

by:magarity
ID: 39782935
row_number isn't really stable enough to count on it for that kind of thing.  Why can't you use the create timestamp in combination with the various ID fields instead? It makes for more conditions in your join clause but should be far more reliable.
0
 
LVL 2

Accepted Solution

by:
alcindor earned 250 total points
ID: 39782950
You could use a cursor as follows:

DECLARE @n int
DECLARE @tc datetime

DECLARE Cursor1 CURSOR FOR
SELECT create_timestamp FROM pas_todays_assessment_ order by create_timestamp

SET @n= 1
OPEN Cursor1
FETCH NEXT FROM Cursor1 into @tc
WHILE @@FETCH_STATUS = 0
   BEGIN
      UPDATE pas_todays_assessment_
      SET txt_dx_prior = @n
      WHERE create_timestamp = @tc
      SET @n = @n + 1

      FETCH NEXT FROM Cursor1 into @tc;
   END;
CLOSE Cursor1;
DEALLOCATE Cursor1;
GO
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 39783002
ROW_NUMBER() in a CTE will work.  In fact, it works perfectly because a CTE is a virtual table, you can perform the update directly against it.

Note: In your query, you filter on person_id and enc_id; therefore, I expect that in the PARTITION BY of the OVER() analytic clause.

Anyway, here is an example:
;WITH cte AS (
/* 
Run this select by itself to verify results match expectations.
*/
    SELECT person_id, enc_id, txt_dx_prior, create_timestamp
         , RN = ROW_NUMBER() 
             OVER(PARITION BY person_id, enc_id
                  ORDER BY create_timestamp DESC)
    FROM pas_todays_assessment_
)
/*
Update through CTE.
*/
UPDATE cte 
SET txt_dx_prior = RN
;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:robthomas09
ID: 39783553
Hi Kevin,

Thanks for your reply. Trying the code out now, any reason why i would be getting this when trying to run the select statement?

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'PARITION'.

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 39784035
typo in PARTITION
;WITH cte AS (
/* 
Run this select by itself to verify results match expectations.
*/
    SELECT person_id, enc_id, txt_dx_prior, create_timestamp
         , RN = ROW_NUMBER() 
             OVER(PARTITION BY person_id, enc_id
                  ORDER BY create_timestamp DESC)
    FROM pas_todays_assessment_
)
/*
Update through CTE.
*/
UPDATE cte 
SET txt_dx_prior = RN
;

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39785214
Good catch, Sharath!  I typed it correctly in the comment, so I guess I should have copy and pasted instead of trusting my typing ability. *smile*

@robthomas09, I am glad you found a solution that met your needs and that I could help.

Best regards and happy coding,

Kevin
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now