Delete SQL DUPLICATE LINES BY SCRIPTING

Ignition
, ,
1 
with tabs as  
(   
select ROW_NUMBER() over (order by SaleOrder) as RowNum ,

 [SaleOrder]
      ,[Date]
      ,[Pos]
      ,[Remark]
      ,[UpdateTime]
  FROM [NTSplicingSCADA].[dbo].[SalesText]
  )
  delete from tabs
  WHERE RowNum<(SELECT MAX(T2.RowNum)
				From tabs T2
				WHERE tabs.SaleOrder=T2.SaleOrder
				and tabs.Pos=T2.Pos
				and tabs.Remark=T2.Remark
				)

In my SQL table , which always contain many lines they are the same data, I want to delete duplicate lines, I can success in sql environment ,but I want to running it in the ignition by means of scripting to trigger upside SQL logic

querydelete="""
with tabs as  
(   
select ROW_NUMBER() over (order by SaleOrder) as RowNum ,

 [SaleOrder]
      ,[Date]
      ,[Pos]
      ,[Remark]
      ,[UpdateTime]
  FROM [NTSplicingSCADA].[dbo].[SalesText]
  )
  delete from tabs
  WHERE RowNum<(SELECT MAX(T2.RowNum)
				From tabs T2
				WHERE tabs.SaleOrder=T2.SaleOrder
				and tabs.Pos=T2.Pos
				and tabs.Remark=T2.Remark
				)

"""
system.db.runQuery(querydelete,'NTSplicingSCADA')

image
image822×829 24.9 KB

But it report fault

2

Use system.db.runUpdateQuery() instead.
https://docs.inductiveautomation.com/display/DOC80/system.db.runUpdateQuery

1 Like
3

It works , thanks a lot

1 Like
4

As an aside, if you want to prevent duplicate rows in the first place, try making an UNIQUE INDEX on the column or columns you want to enforce uniqueness on.

2 Likes