UvfwhNfW

1-- CREATE A CLEAN TEMP TABLE
2
3CREATE TABLE IF NOT EXISTS  TMP_IDN_USER_DATA (
4  tenant_id INTEGER not null,
5  user_name varchar(255) not null,
6  data_key varchar(255) not null,
7  data_value varchar(2048)
8);
9CREATE UNIQUE INDEX IF NOT EXISTS TMP_IDN_USER_DATA_IDX
10 ON TMP_IDN_USER_DATA(tenant_id, user_name, data_key);
11TRUNCATE TMP_IDN_USER_DATA;
12
13
14-- INSERT psswd timetamp TO THE TEMP TABLE 
15
16INSERT INTO TMP_IDN_USER_DATA SELECT 
17 um_user.um_tenant_id, um_user.um_user_name, 
18 'http://wso2.org/claims/identity/lastPasswordUpdateTime', 
19 um_user_attribute.um_attr_value
20FROM um_user INNER JOIN um_user_attribute
21ON um_user.um_id=um_user_attribute.um_user_id
22WHERE um_user_attribute.um_attr_name='lastPasswordChangedTimestamp';
23
24-- checking existing idn_identity_user_data
25SELECT count(*) FROM idn_identity_user_data 
26WHERE data_key='http://wso2.org/claims/identity/lastPasswordUpdateTime';
27
28-- DELETE FROM THE TEMP TABLE EXISTING RECORDS
29
30DELETE FROM TMP_IDN_USER_DATA 
31 WHERE  data_key='http://wso2.org/claims/identity/lastPasswordUpdateTime'
32 AND user_name in (SELECT user_name FROM idn_identity_user_data 
33WHERE data_key='http://wso2.org/claims/identity/lastPasswordUpdateTime');
34
35-- INSERT lastPasswordUpdateTime TO idn_identity_user_data
36
37INSERT INTO idn_identity_user_data SELECT * FROM TMP_IDN_USER_DATA;
38
39-- uncomment when really want to delete
40-- DELETE FROM um_user_attribute WHERE um_attr_name='lastPasswordChangedTimestamp';
41
42DROP TABLE TMP_IDN_USER_DATA;