Adding Where clause to update query

Ignition
1

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

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

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

4

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

5

ok, I will rewrite it and see where I get

6

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

that worked for the update

8

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)