# Updating a value in SQL table using where clause **URL:** \ **Category:** Ignition\ **Created:** [May 27, 2020, 11:17am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073 "2020-05-27T11:17:47Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![vijay.srinivasan](https://avatars.discourse-cdn.com/v4/letter/v/ccd318/32.png) [@vijay.srinivasan](https://forum.inductiveautomation.com/u/vijay.srinivasan)\ **Post date:** [May 27, 2020, 11:17am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/1 "2020-05-27T11:17:47Z")
Hi Guys, I have to write value to a cell in SQL table when a button is pressed in Igntion Screen. I am trying to write SQL query in button’s actionperformed function using queryValue = event.source.parent.getComponent(‘Numeric Label’).value system.db.runUpdateQuery(“UPDATE TestDB SET ComponentRemQuantity = %s” % (queryValue), ‘SQLExpress’) But this updates the value in all the rows of that particular column and i am not able to use a WHERE clause with this query also. Am i doing something wrong or is there any other way to do this? Thanks in Advance…!! ---
**Author:** ![Kfoldesi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kfoldesi/32/11327_2.png) [@Kfoldesi](https://forum.inductiveautomation.com/u/Kfoldesi)\ **Post date:** [May 27, 2020, 11:45am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/2 "2020-05-27T11:45:18Z")
A WHERE clause is appropriate here. Are you getting an error. SQL is applying the change to all rows because the WHERE clause is missing.[https://www.w3schools.com/sql/sql\_where.asp](https://www.w3schools.com/sql/sql_where.asp) ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [May 27, 2020, 11:48am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/3 "2020-05-27T11:48:27Z")
> [@vijay.srinivasan](#): > > queryValue = event.source.parent.getComponent(‘Numeric Label’).value > system.db.runUpdateQuery(“UPDATE TestDB SET ComponentRemQuantity = %s” % (queryValue), ‘SQLExpress’) Should be: > queryValue = event.source.parent.getComponent(‘Numeric Label’).value > RecordID = event.source.parent.getComponent(‘RecordID’).value > databaseConnection = "SQLExpress" > system.db.runPrepUpdate(`"UPDATE TestDB SET ComponentRemQuantity = ? WHERE tblIDField = ? )", [queryValue, RecordID], databaseConnection) Replace the RecordID/tblIDField with the value of the record you are trying to update unique identifier. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [May 27, 2020, 12:26pm UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/4 "2020-05-27T12:26:57Z")
If you truly are trying to update an entire table, your database may have a default SAFE\_MODE type function that prevents this. I know MySQL by default has SAFE\_MODE on and would not allow you to do an update like that, SQL Express may have similar. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [May 27, 2020, 8:51pm UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/5 "2020-05-27T20:51:36Z")
> [@vijay.srinivasan](#): > > and i am not able to use a WHERE clause with this query also. If this is the case, then your only option is to update the entire table. But I suspect that this isn't the case.. ---
**Author:** ![vijay.srinivasan](https://avatars.discourse-cdn.com/v4/letter/v/ccd318/32.png) [@vijay.srinivasan](https://forum.inductiveautomation.com/u/vijay.srinivasan)\ **Post date:** [May 28, 2020, 7:08am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/6 "2020-05-28T07:08:57Z")
yes i got an error with a where clause, but may be because i was trying to use it in the next line than runUpdateQuery command. ---
**Author:** ![vijay.srinivasan](https://avatars.discourse-cdn.com/v4/letter/v/ccd318/32.png) [@vijay.srinivasan](https://forum.inductiveautomation.com/u/vijay.srinivasan)\ **Post date:** [May 28, 2020, 7:14am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/7 "2020-05-28T07:14:56Z")
Thanks for that suggestion… but i got somewhere near i guess. Now i am using the code: queryValue = event.source.parent.getComponent(‘Numeric Label’).value value1=event.source.parent.getComponent(‘Label 2’).text value2=event.source.parent.getComponent(‘Label 3’).text system.db.runUpdateQuery("UPDATE TestDB SET ComponentRemQuantity = %s WHERE ComponentNumber=‘value1’ and OrderName=‘value2’ " % (queryValue), ‘SQLExpress’) The problem here is, the query is not working when i am using ComponentNumber=‘value1’ and OrderName=‘value2’ whereas if i use a direct string value like ComponentNumber=‘ABCD’ and OrderName=‘XYZ’ it works. Does this mean i can’t use an indirect string value in the runUpdateQuery where clause?? I don’t think so. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [May 28, 2020, 9:35am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/8 "2020-05-28T09:35:46Z")
You’re using %s to substitute one variable, you need to use the same for the other two variables. At the moment you’re using fixed strings for your where clause values. The format, which is standard python, is: ‘%s %s %s’ % (1,2,3) Or otherwise, you can use @MMaynard 's suggestion and use runPrepQuery instead, which is far easier to pass variables into as it does all the type conversions for you. I.e. You don’t have to worry about encasing strings in quotes, formatting dates, etc. ---
**Author:** ![vijay.srinivasan](https://avatars.discourse-cdn.com/v4/letter/v/ccd318/32.png) [@vijay.srinivasan](https://forum.inductiveautomation.com/u/vijay.srinivasan)\ **Post date:** [May 29, 2020, 5:48am UTC](https://forum.inductiveautomation.com/t/updating-a-value-in-sql-table-using-where-clause/35073/9 "2020-05-29T05:48:33Z")
I will checkout both those cases. Thanks 🙂