SQL Update Help

Ignition
1

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.

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)
2

It would go after the Set statement:

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

Then add value7 to your args list.

1 Like
3

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.)

4 Likes
4

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

3 Likes
5

@John_Williams

SQL update syntax looks like this: SQL UPDATE Statement

Not like this: SQL INSERT INTO Statement

edit: sorry @lrose replied to wrong person

4 Likes
6

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

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)
7

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.

1 Like
8

Thanks. I was wondering why it had triple quotes.

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)
9

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

{"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.

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

Then your runAction script becomes:

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