Solved

SQL Table Update with Row Number

Posted on 2014-01-15
6
869 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 60

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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 41

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 60

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 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