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:
SELECT rowno = ROW_NUMBER()
PARTITION BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID
ORDER BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID )
) 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.