# PrepUpdate

**URL:** <https://forum.inductiveautomation.com/t/prepupdate/3947>\
**Category:** Ignition\
**Created:** [February 2, 2012, 11:39pm UTC](https://forum.inductiveautomation.com/t/prepupdate/3947 "2012-02-02T23:39:37Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joseph\_Mendonca](https://avatars.discourse-cdn.com/v4/letter/j/58f4c7/32.png) [@Joseph\_Mendonca](https://forum.inductiveautomation.com/u/Joseph_Mendonca)\
**Post date:** [February 2, 2012, 11:39pm UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/1 "2012-02-02T23:39:37Z")

</div>

I’m trying to delete all of the data in a table. How do you wild card the arg?

system.db.runPrepUpdate(“DELETE FROM history\_pens WHERE GROUP\_NAME = ?”, [\*])

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [February 3, 2012, 12:32am UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/2 "2012-02-03T00:32:52Z")

</div>

This query doesn’t need a wildcard, if you want to delete every row you can do this:

system.db.runPrepUpdate(“DELETE FROM history\_pens”)

You can find out a lot about SQL Queries from places like [sqlzoo](http://sqlzoo.net/)

---

<div class="post-metadata">

**Author:** ![Joseph\_Mendonca](https://avatars.discourse-cdn.com/v4/letter/j/58f4c7/32.png) [@Joseph\_Mendonca](https://forum.inductiveautomation.com/u/Joseph_Mendonca)\
**Post date:** [February 7, 2012, 4:22am UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/3 "2012-02-07T04:22:59Z")

</div>

Tried that as well as other variations with no success. Had to query from the MS enterprise manager to get it to work.

---

<div class="post-metadata">

**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\
**Post date:** [February 7, 2012, 5:44pm UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/4 "2012-02-07T17:44:45Z")

</div>

I’m glad you got it working. For future reference, the format you are using expects a variable name in the square brackets [] like:

`myvariable = "the group name is green"
system.db.runPrepUpdate("DELETE FROM history_pens WHERE GROUP_NAME = ?", [myvariable])`

---

<div class="post-metadata">

**Author:** ![Joseph\_Mendonca](https://avatars.discourse-cdn.com/v4/letter/j/58f4c7/32.png) [@Joseph\_Mendonca](https://forum.inductiveautomation.com/u/Joseph_Mendonca)\
**Post date:** [March 14, 2012, 3:16am UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/5 "2012-03-14T03:16:28Z")

</div>

Tried system.db.runPrepUpdate(“DELETE FROM history\_pens”) from Ignition using both MSSQL and Postgres databases without success. It does work when querying from the MSSQL and Postgres query analyzer not from Ignition.

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [March 14, 2012, 6:07am UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/6 "2012-03-14T06:07:44Z")

</div>

runPrepUpdate requires a list of values, even if not holders are in teh query, so you will have to run

```auto
system.db.runPrepUpdate("DELETE FROM history_pens",[])
```

or

```auto
system.db.runUpdateQuery("DELETE FROM history_pens")
```

---

<div class="post-metadata">

**Author:** ![Joseph\_Mendonca](https://avatars.discourse-cdn.com/v4/letter/j/58f4c7/32.png) [@Joseph\_Mendonca](https://forum.inductiveautomation.com/u/Joseph_Mendonca)\
**Post date:** [March 15, 2012, 12:55am UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/7 "2012-03-15T00:55:52Z")

</div>

This works. Thanks

system.db.runPrepUpdate(“DELETE FROM history\_pens”,[])

---

<div class="post-metadata">

**Author:** ![Greg.Buehler](https://avatars.discourse-cdn.com/v4/letter/g/ecc23a/32.png) [@Greg.Buehler](https://forum.inductiveautomation.com/u/Greg.Buehler)\
**Post date:** [March 15, 2012, 1:33am UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/8 "2012-03-15T01:33:38Z")

</div>

I’m pretty sure you can fall back to:

```auto
system.db.runScalarQuery("DELETE FROM history_pens")
```

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [March 15, 2012, 2:59pm UTC](https://forum.inductiveautomation.com/t/prepupdate/3947/9 "2012-03-15T14:59:46Z")

</div>

No, for DELETES, UPDATES, and INSERTS you should use either:

1. system.db.runPrepUpdate or
2. system.db.runUpdateQuery

It is recommended to use runPrepUpdate because:

[quote]1. This method avoids the problematic technique of concatenating user input inside of a query, which can lead to syntax errors, or worse, a nasty security problem called a SQL injection attack. For example, if you have a user-supplied string that is used in a WHERE clause, you use single-quotes to enclose the string to make the query valid. What happens in the user has a single-quote in their text? Your query will fail. Prepared statements are immune to this problem.

2.This is the only way to write an INSERT or UPDATE query that has binary or BLOB data. Using BLOBs can be very hand for storing images or reports in the database, where all clients have access to them.  
[/quote]