# Delete SQL DUPLICATE LINES BY SCRIPTING

**URL:** <https://forum.inductiveautomation.com/t/delete-sql-duplicate-lines-by-scripting/39489>\
**Category:** Ignition\
**Tags:** scripting, database, ignition80\
**Created:** [October 8, 2020, 10:53am UTC](https://forum.inductiveautomation.com/t/delete-sql-duplicate-lines-by-scripting/39489 "2020-10-08T10:53:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [October 8, 2020, 10:53am UTC](https://forum.inductiveautomation.com/t/delete-sql-duplicate-lines-by-scripting/39489/1 "2020-10-08T10:53:56Z")

</div>

```auto
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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/9/7962a593848ab635f330deb643a04251edebb11f.png)  
But it report fault

---

<div class="post-metadata">

**Author:** ![rbabcock](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbabcock/32/13425_2.png) [@rbabcock](https://forum.inductiveautomation.com/u/rbabcock)\
**Post date:** [October 8, 2020, 10:56am UTC](https://forum.inductiveautomation.com/t/delete-sql-duplicate-lines-by-scripting/39489/2 "2020-10-08T10:56:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [October 8, 2020, 11:05am UTC](https://forum.inductiveautomation.com/t/delete-sql-duplicate-lines-by-scripting/39489/3 "2020-10-08T11:05:24Z")

</div>

It works , thanks a lot

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 8, 2020, 12:05pm UTC](https://forum.inductiveautomation.com/t/delete-sql-duplicate-lines-by-scripting/39489/4 "2020-10-08T12:05:45Z")

</div>

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.