Author: bleny Date: 2014-04-30 16:25:45 +0200 (Wed, 30 Apr 2014) New Revision: 1919 Url: http://forge.codelutin.com/projects/wao/repository/revisions/1919 Log: fixes #4920 remove duplicate entry from DB Modified: trunk/wao-persistence/src/main/resources/db/migration/V4_0__clean-database.sql Modified: trunk/wao-persistence/src/main/resources/db/migration/V4_0__clean-database.sql =================================================================== --- trunk/wao-persistence/src/main/resources/db/migration/V4_0__clean-database.sql 2014-04-30 14:03:16 UTC (rev 1918) +++ trunk/wao-persistence/src/main/resources/db/migration/V4_0__clean-database.sql 2014-04-30 14:25:45 UTC (rev 1919) @@ -2,3 +2,17 @@ -- we no longer user topia migration service drop table tms_version; +-- Il y a des doublons dans la table qui stocke les informations sur le lien entre ligne du plan et navires éligibles. +-- Un même navire peut être associé en tant qu'éligible plusieurs fois. +-- On rectifie le tir en supprimant les doublons et en ajoutant une contrainte d'unicité + +delete from elligibleboat +where topiaId in ( select eb.topiaId + from elligibleboat eb + where eb.sampleRow in (select eb2.samplerow from elligibleboat eb2 group by samplerow, boat having count(topiaId) > 1) + and eb.boat in (select eb2.boat from elligibleboat eb2 group by samplerow, boat having count(topiaId) > 1) + and eb.topiaId not in (select eb2.topiaId from elligibleboat eb2 where eb2.boat = eb.boat and eb2.sampleRow = eb.sampleRow order by topiacreatedate desc limit 1)); + +alter table elligibleboat add constraint samplerow_boat_uniqueness unique (boat, samplerow); + +