Fixing Duplicate Saleslogix FieldIndex Values in the SECTABLEDEFS

I had to fix a client’s SECTABLEDEFS table that contained duplicate FieldIndex values.  FieldIndex is used by the Saleslogix field level security functionality.  Each FieldIndex should be a unique incremental number.  Here is the SQL I came up with.  This worked for me, but with any SQL statements, run only if you know what you are doing, and even then, do it in a test copy of the database to ensure no unexpected outcomes are encountered!

update SECTABLEDEFS set

FIELDINDEX = z.newindex

from

(select a.fieldname, a.fieldindex, t.fieldindex+x.topindex newindex from

sectabledefs a inner join

   (

   select ROW_NUMBER() over(order by tablename,a.fieldname) fieldindex, tablename,a.fieldname

    from SECTABLEDEFS a inner join   

    (select min(fieldname) fieldname, fieldindex

   from

            sysdba.sectabledefs

      group by

            fieldindex 

      having

            count(fieldindex)>1) x on a.FIELDINDEX=x.FIELDINDEX and a.FIELDNAME=x.fieldname             

    group by tablename,a.fieldname

   ) t on a.FIELDNAME=t.FIELDNAME

    and

   a.TABLENAME=t.TABLENAME

   ,

      (select MAX(fieldindex) topindex from SECTABLEDEFS) x

–order by t.fieldindex+x.topindex ,a.FIELDINDEX

) z

where SECTABLEDEFS.FIELDINDEX=z.FIELDINDEX and SECTABLEDEFS.FIELDNAME=z.FIELDNAME