# Adding Where clause to update query

**URL:** <https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538>\
**Category:** Ignition\
**Created:** [September 9, 2020, 8:39pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538 "2020-09-09T20:39:42Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 8:39pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/1 "2020-09-09T20:39:42Z")

</div>

I am trying to figure out where to add my where clause to the update query.

```auto
query = "Update EquipWorkOrder Set (DateTm, PlantLocID, StatusID,
  MaintLocationID, EquipmentID, EquipTypeID, CategoryID , CrewID, 
  MeterReading, Description, Resolution, Comments) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"
args = [event.source.parent.getComponent('DateTm').date,1,event.source.parent
  .getComponent('Status').selectedValue,event.source.parent.getComponent('MaintLocation').selectedValue,event.source.parent.getComponent('Equipment').selectedValue,event.source.parent
  .getComponent('EquipTypeID').intValue,event.source.parent
  .getComponent('Category').selectedValue,event.source.parent
  .getComponent('Crew').selectedValue,event.source.parent
  .getComponent('Meter Reading').floatValue,event.source.parent
  .getComponent('Description').text,event.source.parent
  .getComponent('Resolution').text,event.source.parent
  .getComponent('Comments').text]
system.db.runPrepUpdate(query, args)

```

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [September 9, 2020, 8:47pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/2 "2020-09-09T20:47:13Z")

</div>

The where clause typically goes at the end of the query, but the usual syntax of an UPDATE query doesn’t match what you have written. What database are you using?

From [https://www.w3schools.com/sql/sql\_update.asp](https://www.w3schools.com/sql/sql_update.asp)

```auto
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

```

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 9:14pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/3 "2020-09-09T21:14:39Z")

</div>

ms sql. I have written queries before with where clauses in it . I am not for sure how to do it in this format

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [September 9, 2020, 9:20pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/4 "2020-09-09T21:20:07Z")

</div>

@jshaffer09, I think you’re getting INSERT and UPDATE mixed up. Your query starts with UPDATE but uses INSERT syntax (you don’t insert with where clauses). Change your UPDATE syntax to match @zacht 's post above.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 9:21pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/5 "2020-09-09T21:21:08Z")

</div>

ok, I will rewrite it and see where I get

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 9:43pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/6 "2020-09-09T21:43:49Z")

</div>

Like this

```auto
query = "Update EquipWorkOrder Set DateTm = ?,PlantLocID = ?, StatusID = ?, MaintLocationID = ? ,EquipmentID = ?,EquipTypeID = ?,CategoryID = ?, CrewID = ?,MeterReading = ?,Description = ?,Resolution= ?,Comments= ? Where EquipWorkOrderID = ?"
		args = [event.source.parent.getComponent('DateTm').date,1,event.source.parent.getComponent('Status').selectedValue,event.source.parent.getComponent('MaintLocation').selectedValue,event.source.parent.getComponent('Equipment').selectedValue,event.source.parent.getComponent('EquipTypeID').intValue,event.source.parent.getComponent('Category').selectedValue,event.source.parent.getComponent('Crew').selectedValue,event.source.parent.getComponent('Meter Reading').floatValue,event.source.parent.getComponent('Description').text,event.source.parent.getComponent('Resolution').text,event.source.parent.getComponent('Comments').text,event.source.parent.getComponent('Table').EquipWorkOrderID]
		system.db.runPrepUpdate(query, args)

```

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 10:11pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/7 "2020-09-09T22:11:21Z")

</div>

that worked for the update

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [September 9, 2020, 11:07pm UTC](https://forum.inductiveautomation.com/t/adding-where-clause-to-update-query/38538/8 "2020-09-09T23:07:25Z")

</div>

I’m glad you got it working. Working is working, but tweaking the formatting can make it more readable. You certainly don’t have to do this, but I’m on a readability kick (crusade?) at the moment.

```auto
query = """
UPDATE EquipWorkOrder 
SET DateTm = ?,
	PlantLocID = ?,
	StatusID = ?,
	MaintLocationID = ? ,
	EquipmentID = ?,
	EquipTypeID = ?,
	CategoryID = ?,
	CrewID = ?,
	MeterReading = ?,
	Description = ?,
	Resolution= ?,
	Comments= ?
WHERE EquipWorkOrderID = ?
"""
args = [
	event.source.parent.getComponent('DateTm').date,
	1,
	event.source.parent.getComponent('Status').selectedValue,
	event.source.parent.getComponent('MaintLocation').selectedValue,
	event.source.parent.getComponent('Equipment').selectedValue,
	event.source.parent.getComponent('EquipTypeID').intValue,
	event.source.parent.getComponent('Category').selectedValue,
	event.source.parent.getComponent('Crew').selectedValue,
	event.source.parent.getComponent('Meter Reading').floatValue,
	event.source.parent.getComponent('Description').text,
	event.source.parent.getComponent('Resolution').text,
	event.source.parent.getComponent('Comments').text,
	event.source.parent.getComponent('Table').EquipWorkOrderID
]
system.db.runPrepUpdate(query, args)

```