How To Clean Duplicated Elements From Acumatica Database
30 July 2020
Hello everybody,
Today I want to leave a short snapshot which addresses following issue.
One of my friends got table in Acumatica created, but for some reason at DB level he decided not to set there any field as part of Primary key. He had IsKey attributes set only at DAC class of Acumatica, but not at Database level. As outcome he got duplicated items at database level, and it was needed somehow to delete duplicated items. How to achieve it?
After a bit of research, I've come with T-SQL code like this:
DELETE Record FROM ( SELECT rowno = ROW_NUMBER() OVER ( PARTITION BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID ORDER BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID ) FROM ACGLiquidFamilyPrice ) AS Record WHERE Record.rowno > 1
Columns LiquidFamilyCD, PriceEntryDateTime, CompanyID - are key fields.
ROW_NUMBER in connection with Partition by allows to delete only records, which are more then one.