Fixing 'see applicant' issue in patstat TLS207

Among the gift brought from EPO with oct. 2013 version, there is a change that is quite a worstening of data quality, since for a big number of applications (140.700: all of them at EP application authority) instead of the correct reference to the inventor person_id TLS207 lists person id 96 whose name (see applicant) do not let us presume it is a valid entry...

In order to fix it for the great majority of the records (140.680 of them) who list only one inventor (the famous 'see applicant') I wrote this lines of code that should fix the issue in mysql.
are left out about 20 records that have also inventors  other than 'see applicant' so a hand made correctin and check should be performed.



-- fixes for SEE APPLICANT (person id 96) on TLS207

use patstat;

-- back up old TLS207

create table tls207_pers_appln_back as select * from tls207_pers_appln;

-- temp table with

drop table if exists t01;

create table t01
select u.appln_id from tls207_pers_appln u inner join
(SELECT * FROM tls207_pers_appln t
WHERE t.`PERSON_ID`=96 and invt_seq_nr =1) as t on t.appln_id = u.appln_id
group by u.appln_id
having max(u.invt_seq_nr = 1);

alter table t01 add index id1(appln_id);

-- update : keep applicants order

update  tls207_pers_appln a inner join t01 t on t.appln_id = a.appln_id
set a.invt_seq_nr =   a.applt_seq_nr
where  a.invt_seq_nr = 0 and    a.applt_seq_nr > 0;

-- deletes old inventor 96 see also in updated applications

delete a.* from  tls207_pers_appln a inner join t01 t on t.appln_id = a.appln_id
where person_id = 96;

-- check: tabel should be empty

drop table if exists tchk01;

create table tchk01
Select t7.APPLN_ID, t7.INVT_SEQ_NR, Count(t7.PERSON_ID) as c
From   tls207_pers_appln t7
where t7.INVT_SEQ_NR > 0
Group By t7.APPLN_ID, t7.INVT_SEQ_NR
Having   Count(t7.PERSON_ID) > 1


-- exit drops temp tables

drop table t01;