Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/patient_nums/update_patient_numbers_in_patient_set_collection.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 2.5 KB
Line 
1/****************
2 * Script 4: DEID
3 ****************
4 * Do not run this script independently of: update_patient_set_numbers.sql
5 *                                          create_deid_patient_mapping.sql
6 *                                          insert_deid_patient_mapping.sql
7 * This script is not idempotent.  The update should only be run once.
8 * And the in place reversal is... complicated:
9 * Step 1: update blueherondata.qt_patient_set_collection set patient_num = original_patient_num;
10 * Step 2: modify update_patient_set_numbers.sql's creation of the view query_to_backup_id
11 *         to use a different "on" statement.  Search: "KEYWORD:REVERSE"
12 * Step 3: Run all scripts as normal.
13 *
14 * Another option is to reload the QT tables specifically qt_patient_set_collection
15 * and try again.
16 */
17
18
19select original_patient_num from blueherondata.qt_patient_set_collection where 1=0;
20/*
21!if the above line fails then the qt_patient_set_collection needs to be updated
22!with the additional "original_patient_num" column.
23!Run the below script:
24alter table blueherondata.qt_patient_set_collection add original_patient_num number(10,0);
25update blueherondata.qt_patient_set_collection set original_patient_num = patient_num;
26*/
27
28update
29(select up.patient_num, pmap.current_patient_num
30 from blueherondata.qt_patient_set_collection up
31 join only_needed_mappings pmap on up.patient_set_coll_id = pmap.patient_set_coll_id)
32set patient_num = current_patient_num;
33
34--Continue to Script 5: expire_old_patient_sets.sql on the id database
35
36
37/** UPDATE TESTING **
38
39create table qt_patient_set_col_playground as
40select '1' as patient_set_coll_id, 'A' as patient_num from dual union
41select '2' as patient_set_coll_id, 'B' as patient_num from dual union
42select '3' as patient_set_coll_id, 'C' as patient_num from dual union
43select '4' as patient_set_coll_id, 'D' as patient_num from dual;
44
45alter table qt_patient_set_col_playground add constraint playground_pk primary key(patient_set_coll_id);
46
47create table only_needed_mappings as
48select '1' as patient_set_coll_id, 'E' as current_patient_num from dual union
49select '2' as patient_set_coll_id, 'F' as current_patient_num from dual;
50
51alter table only_needed_mappings add constraint only_needed_mappings_pk primary key(patient_set_coll_id);
52
53
54update
55(    select up.patient_num, pmap.current_patient_num
56     from qt_patient_set_col_playground up
57     join only_needed_mappings pmap on up.patient_set_coll_id = pmap.patient_set_coll_id)
58set patient_num = current_patient_num
59;
60
61select * from qt_patient_set_col_playground;
62**************/
Note: See TracBrowser for help on using the repository browser.