Note: The [Order] field is not unique in the database. The [Namespace] field is for multi-tenancy support.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
:setvar NAMESPACE "demo" --find records with duplicated [Order] values SELECT [Duplicated Order Value] = count(*) ,[Namespace] --,[Group_Id] ,[Order] FROM [peeps].[FieldRegistration] --[FieldCaseManagement] where [Namespace] = '$(NAMESPACE)' group by [Namespace] --,[Group_Id] ,[Order] having count(*) > 1 order by --[Group_Id], [Order] --update [Order] values update peeps.[FieldRegistration] set [Order] = x2.[Row] from ( select [Namespace],[Name],[Field_Id],[Row] from ( select f.[Name] ,fr.[Field_Id] ,fr.[Namespace] ,fr.[Order] ,[Row] = (row_number() over (partition by fr.[Namespace] order by fr.[Order],f.[Name]) + 8) -- the +8 is because there are nine system fields stored elsewhere (only for [FieldRegistration] table). from [peeps].[FieldRegistration] fr join [peeps].[Field] f on f.[Id] = fr.[Field_Id] ) as x1 where [Row] <> [Order] ) as x2 where peeps.[FieldRegistration].[Namespace] = x2.[Namespace] and peeps.[FieldRegistration].[Field_Id] = x2.[Field_Id] and peeps.[FieldRegistration].[Namespace] = '$(NAMESPACE)' --display results select * from peeps.[FieldRegistration] where [Namespace] = '$(NAMESPACE)' order by [Namespace],[Order] |