Solved

SQL Table Update with Row Number

Posted on 2014-01-15
6
858 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

17 Experts available now in Live!

Get 1:1 Help Now