• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 885
  • Last Modified:

SQL Table Update with Row Number

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
robthomas09
Asked:
robthomas09
2 Solutions
 
magarityCommented:
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
 
alcindorCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
robthomas09Author Commented:
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
 
SharathData EngineerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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