Adding Where clause to update query

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

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

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

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
3 Likes

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

1 Like

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

1 Like

ok, I will rewrite it and see where I get

1 Like

Like this

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

that worked for the update

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.

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)