# Not able to delete row in MSSQL via :delete parameter

**URL:** <https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315>\
**Category:** General Discussion\
**Created:** [May 13, 2024, 2:57pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315 "2024-05-13T14:57:01Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Thomas\_Salisbury](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/thomas_salisbury/32/62046_2.png) [@Thomas\_Salisbury](https://forum.inductiveautomation.com/u/Thomas_Salisbury)\
**Post date:** [May 13, 2024, 2:57pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/1 "2024-05-13T14:57:01Z")

</div>

HI all,

I am trying to manage a best practice for my system design, and one thought was to have this Named Query.

```python
MERGE INTO company AS Target
USING (VALUES (:name, :parentId))
      AS Source (name, parentId)
ON Target.name = Source.name
WHEN NOT MATCHED THEN
    INSERT (name, parentId)
    VALUES (Source.name, Source.parentId)
WHEN MATCHED AND :delete = TRUE THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET
        name = Source.name,
        parentId = Source.parentId;

```

my thought was to have a merge/update/insert and delete handled by one query.  
The issue coming up is, the insert isn't working, the delete throws an error:

> com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'TRUE'

Any thoughts are greatly appreciated.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 13, 2024, 3:00pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/2 "2024-05-13T15:00:31Z")

</div>

You can't use a Value type parameter as a column name. You have to use a QueryString type - but you need to be aware of the risks.

> **[Named Query Parameters | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/platform/sql-in-ignition/named-queries/named-query-parameters#types-of-named-query-parameters)**
>
> Parameters allow you to make Named Queries dynamic. They act as placeholders you can pass values into when requesting the query to execute. Other resources in Ignition can then pass arguments into the parameters. The exact implementation depends on...

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 13, 2024, 3:02pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/3 "2024-05-13T15:02:39Z")

</div>

Also, MS SQL Server doesn't implement the SQL Standard TRUE and FALSE literals--it doesn't support the BOOLEAN data type at all. When passing through JDBC to SQL Server, booleans are coerced to `tinyint` zero or one.

{Just say NO to MS SQL Server!}

---

<div class="post-metadata">

**Author:** ![Thomas\_Salisbury](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/thomas_salisbury/32/62046_2.png) [@Thomas\_Salisbury](https://forum.inductiveautomation.com/u/Thomas_Salisbury)\
**Post date:** [May 13, 2024, 3:04pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/4 "2024-05-13T15:04:39Z")

</div>

I wish I could... Thats what we get access too. Unfortunately, I can't control that. I have to work with what my company provides.  
I get the sentiment.

---

<div class="post-metadata">

**Author:** ![Thomas\_Salisbury](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/thomas_salisbury/32/62046_2.png) [@Thomas\_Salisbury](https://forum.inductiveautomation.com/u/Thomas_Salisbury)\
**Post date:** [May 13, 2024, 3:05pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/5 "2024-05-13T15:05:32Z")

</div>

Thank you. I am not allowing any user input to that parameter. It is 100% hard code.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 13, 2024, 3:08pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/6 "2024-05-13T15:08:52Z")

</div>

> [@Thomas\_Salisbury](#):
>
> I can't control that

Noted, but you can complain about it when MS stupidity puts you in a bind. (BTW, SQL Server's MERGE statement is racy--it can give errors by chance on non-conflicting inserts--where PostgreSQL's ON CONFLICT is deterministic.)

If you don't complain, your company will never get the hint that MS SQL Server is [expletive]. There is a reason that all of the other major players use PostgreSQL as the foundation of their big data offerings.

---

<div class="post-metadata">

**Author:** ![Thomas\_Salisbury](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/thomas_salisbury/32/62046_2.png) [@Thomas\_Salisbury](https://forum.inductiveautomation.com/u/Thomas_Salisbury)\
**Post date:** [May 13, 2024, 3:13pm UTC](https://forum.inductiveautomation.com/t/not-able-to-delete-row-in-mssql-via-delete-parameter/90315/7 "2024-05-13T15:13:21Z")

</div>

Well, let me see if I can get a PostgreSQL server for this project. 🙂