# Best Practice - Named Query Updates & Deletes

**URL:** <https://forum.inductiveautomation.com/t/best-practice-named-query-updates-deletes/100404>\
**Category:** Ignition\
**Created:** [January 2, 2025, 8:41pm UTC](https://forum.inductiveautomation.com/t/best-practice-named-query-updates-deletes/100404 "2025-01-02T20:41:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Justin\_Mai](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@Justin\_Mai](https://forum.inductiveautomation.com/u/Justin_Mai)\
**Post date:** [January 2, 2025, 8:41pm UTC](https://forum.inductiveautomation.com/t/best-practice-named-query-updates-deletes/100404/1 "2025-01-02T20:41:36Z")

</div>

Any best practices for named queries that are intended to UPDATE or DELETE a single record in a table?

For example I have a lot of named queries like:

```python
DELETE FROM my_example_table
WHERE id = :id

```

I can't help but be a little concerned that this SQL statement could go from deleting a single record to deleting an entire table by the following accidents:

- WHERE condition row of text is deleted or commented out
- 'id' gets a colon placed in front of it
- colon gets removed from the ':id'

I'm relatively new to databases and the permanence of running the wrong command spooks me a bit. The couple things I've done to prevent these potential accidents is to add a comment line beforehand, and prefix the WHERE condition's parameter name to have a little bit bigger different between the table's column name:

```python
DELETE FROM my_example_table
--- !!!CAUTION WHERE CONDITION!!!
WHERE id = :param_id

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 2, 2025, 8:45pm UTC](https://forum.inductiveautomation.com/t/best-practice-named-query-updates-deletes/100404/2 "2025-01-02T20:45:43Z")

</div>

1. Add a limit clause

```python
DELETE FROM my_example_table
WHERE id = :param_id
LIMIT 1

```

1. Do not really delete the record, instead add an archived field, make it a key, then when users _delete_ something, archive it instead. Then you can really delete it later via a script if the archived date is older than _x_ weeks or similar.

---

<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:** [January 2, 2025, 9:27pm UTC](https://forum.inductiveautomation.com/t/best-practice-named-query-updates-deletes/100404/3 "2025-01-02T21:27:07Z")

</div>

Also, not all JDBC drivers will handle the comment properly (comments are SQL **scripting** ) because they are not part of the JDBC standard.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 2, 2025, 9:31pm UTC](https://forum.inductiveautomation.com/t/best-practice-named-query-updates-deletes/100404/4 "2025-01-02T21:31:33Z")

</div>

> [@Justin\_Mai](#):
>
> the permanence of running the wrong command spooks me a bit

I've been working with databases for 15+ years (probably longer really) and this is still true for me. IMO, this is a great mindset to have when approaching any UPDATE operation (delete or otherwise).

In addition to @dkhayes117 recommendation for achieving deleted rows, I would also recommend having a proper database archiving scheme. There will come a day when you or another DBA does something you which you hadn't and having a good disaster recovery plan in place will make you glad you went through the effort.

> [@Justin\_Mai](#):
>
> I've done to prevent these potential accidents is to add a comment line beforehand

Unfortunately, even though I understand the sentiment. Adding a comment makes this a SQL script, which support for is not enforced in the JDBC specification. And while it may work with your database now, there is nothing in place to insure that it continues to work in the future, or if you move to another DB platform. You might be pulling your hair out and this have been the issue all along.