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
zacht
September 9, 2020, 8:47pm
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 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
zacht
September 9, 2020, 11:07pm
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)