# SQL Update Help

**URL:** <https://forum.inductiveautomation.com/t/sql-update-help/86837>\
**Category:** Ignition\
**Created:** [March 5, 2024, 4:40pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837 "2024-03-05T16:40:50Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![John\_Williams](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@John\_Williams](https://forum.inductiveautomation.com/u/John_Williams)\
**Post date:** [March 5, 2024, 4:40pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/1 "2024-03-05T16:40:50Z")

</div>

I don't understand where i need to put the Where clause in my SQL update script. This is on an action performed script on a button and i created this from an Insert script that works. I can Insert and Delete from the table but i can't figure out where to put the Where in the query so it updates the data based on the UniqueID. I'm using MSSQL if that helps.

```auto
def runAction(self, event):
	value1 = self.custom.ParamIDNew
	value2 = self.custom.POMPropertyPKNew
	value3 = self.custom.POMPropertyNameNew
	value4 = self.custom.POMMultiValueNameNew
	value5 = self.custom.UserName
	value6 = self.custom.Comments
	value7 = self.custom.UniqueID

	if value6 !="":     
		query = "UPDATE MESParameterIDs (Parameter_ID,POMPropertyPK,POMPropertyName,POMMultiValueName,UserName,Comments) VALUES (?,?,?,?,?,?)"
		args = [value1,value2,value3,value4,value5,value6]
		system.db.runPrepUpdate(query, args)

```

---

<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:** [March 5, 2024, 4:49pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/2 "2024-03-05T16:49:02Z")

</div>

It would go after the Set statement:

```auto
query = """
UPDATE MESParameterIDs
    SET Parameter_ID = ?,
    POMPropertyPK = ?,
    POMPropertyName = ?,
    POMMultiValueName = ?,
    UserName = ?,
    Comments = ?
WHERE UniqueID = ?
"""

```

Then add `value7` to your args list.

---

<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:** [March 5, 2024, 4:58pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/3 "2024-03-05T16:58:54Z")

</div>

I've never seen a SQL UPDATE statement with that syntax. Go back to your DB's user manual.

(Update is totally different from Insert.)

---

<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:** [March 5, 2024, 5:01pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/4 "2024-03-05T17:01:29Z")

</div>

You're right, as usual. ID10T error. Corrected.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [March 5, 2024, 5:09pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/5 "2024-03-05T17:09:20Z")

</div>

@John_Williams

SQL update syntax looks like this: [SQL UPDATE Statement](https://www.w3schools.com/sql/sql_update.asp)

Not like this: [SQL INSERT INTO Statement](https://www.w3schools.com/sql/sql_insert.asp)

edit: sorry @lrose replied to wrong person

---

<div class="post-metadata">

**Author:** ![John\_Williams](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@John\_Williams](https://forum.inductiveautomation.com/u/John_Williams)\
**Post date:** [March 5, 2024, 6:45pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/6 "2024-03-05T18:45:22Z")

</div>

Thanks guys. I don't know what i was thinking. Posting the updated script for anyone else that might need it below.

```auto
def runAction(self, event):
	value1 = self.custom.ParamIDNew
	value2 = self.custom.POMPropertyPKNew
	value3 = self.custom.POMPropertyNameNew
	value4 = self.custom.POMMultiValueNameNew
	value5 = self.custom.UserName
	value6 = self.custom.Comments
	value7 = self.custom.UniqueID
	if value6 !="":     
		query = "UPDATE MESParameterIDs SET Parameter_ID = ?,POMPropertyPK = ?,POMPropertyName = ?,POMMultiValueName = ?,UserName =?,Comments = ? WHERE UniqueID = ?"
		args = [value1,value2,value3,value4,value5,value6,value7]
		system.db.runPrepUpdate(query, args)

```

---

<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:** [March 5, 2024, 6:54pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/7 "2024-03-05T18:54:27Z")

</div>

Tips: Add a space after commas in your queries and lists of arguments - same as regular English. It makes reading easier and aids wordwrap in some circumstances. Use the triple-quotes to wrap SQL query strings as shown by lrose.

---

<div class="post-metadata">

**Author:** ![John\_Williams](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@John\_Williams](https://forum.inductiveautomation.com/u/John_Williams)\
**Post date:** [March 5, 2024, 7:11pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/8 "2024-03-05T19:11:16Z")

</div>

Thanks. I was wondering why it had triple quotes.

```auto
def runAction(self, event):
	value1 = self.custom.ParamIDNew
	value2 = self.custom.POMPropertyPKNew
	value3 = self.custom.POMPropertyNameNew
	value4 = self.custom.POMMultiValueNameNew
	value5 = self.custom.UserName
	value6 = self.custom.Comments
	value7 = self.custom.UniqueID
	if value6 !="":     
		query = """
		UPDATE MESParameterIDs 
		SET Parameter_ID = ?, 
		POMPropertyPK = ?, 
		POMPropertyName = ?, 
		POMMultiValueName = ?, 
		UserName = ?, 
		Comments = ? 
		WHERE UniqueID = ?
		"""
		args = [value1, value2, value3, value4, value5, value6, value7]
		system.db.runPrepUpdate(query, args)

```

---

<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:** [March 5, 2024, 7:25pm UTC](https://forum.inductiveautomation.com/t/sql-update-help/86837/9 "2024-03-05T19:25:21Z")

</div>

Personally, I would make a custom property of object type with a structure like this:

```auto
{"POM": {
    "ID",
    "PropertyPK",
    "PropertyName",
    "MultiValueName",
    "UserName",
    "Comments",
    "UniqueID"
}

```

If each of those things is bound to something, then use an Expression Structure to bind all of them.

Create a parameterized Named Query, and make sure that your parameters are named the same as the keys in your structure.

```sql
UPDATE MESParameterIDs
SET Parameter_ID = :ID
     POMPropertyPK = :PropertyPK
     POMPropertyName = :PropertyName
     POMMultiValueName = :MultiValueName
     UserName = :UserName
     Comments = :Comments
WHERE UniqueID = :UniqueID

```

Then your `runAction` script becomes:

```auto
def runAction(self, event):
    system.db.runNamedQuery("Path/To/NamedQuery", self.custom.POM)

```