# Database update syntax

**URL:** <https://forum.inductiveautomation.com/t/database-update-syntax/6089>\
**Category:** Ignition\
**Created:** [March 23, 2013, 4:40am UTC](https://forum.inductiveautomation.com/t/database-update-syntax/6089 "2013-03-23T04:40:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![destino](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@destino](https://forum.inductiveautomation.com/u/destino)\
**Post date:** [March 23, 2013, 4:40am UTC](https://forum.inductiveautomation.com/t/database-update-syntax/6089/1 "2013-03-23T04:40:00Z")

</div>

Greetings

I am very new at ignition.

What I am trying to do is update a table with data that I have placed in variables.

The syntax for update to the database is basically correct but I’m not sure how I get the variables into the where clause.

There don’t seem to be any examples of this in the help.

Just wondering if anyone could help. I suspect that it all to do with quotes etc…

Here is the syntax of my update.

All the variables have had data assigned to them sensor1-4 are floats the rest are strings.

```
system.db.runUpdateQuery ("UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly")

```

This is the error message:

Traceback (most recent call last):

File “event:propertyChange”, line 38, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(UPDATE tempwheeldiameter SET sensor1 = ln\_sensor1, sensor2 = ln\_sensor2, sensor3 = ln\_sensor3, sensor4 = ln\_sensor4 WHERE WHEEL\_RFID == ls\_rfid AND OPERATORCODE == ls\_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls\_assembly, , , false)

```
caused by Exception: Error executing system.db.runUpdateQuery(UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly, , , false)
caused by GatewayException: SQL error for "UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue' at line 1
caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue' at line 1

```

Ignition v7.5.6 (b1317)  
Java: Sun Microsystems Inc. 1.6.0\_31

I think I may be close to success with this issue.

Ive added the following code and I am not getting errors. The table is not being updated but that is likely to be a data issue.

```
ls_querystring = "UPDATE tempwheeldiameter SET "
ls_querystring = ls_querystring + " sensor1 = " + str(ln_sensor1) 
ls_querystring = ls_querystring + ", sensor2 = " + str(ln_sensor2) 
ls_querystring = ls_querystring + ", sensor3 = " + str(ln_sensor3) 
ls_querystring = ls_querystring + ", sensor4 = " + str(ln_sensor4) 
ls_querystring = ls_querystring + " WHERE WHEEL_RFID = " + "'" + ls_rfid + "'"
ls_querystring = ls_querystring + " AND OPERATORCODE = " + "'" + ls_operatorcode + "'"
ls_querystring = ls_querystring + " AND RADIALLOCATION = " + "'" + str(slidervalue) + "'"
ls_querystring = ls_querystring + " AND WHEELMODEL = " + "'" + ls_assembly + "'"
system.gui.messageBox(ls_querystring)
system.db.runUpdateQuery(ls_querystring)
```

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [March 23, 2013, 11:28am UTC](https://forum.inductiveautomation.com/t/database-update-syntax/6089/2 "2013-03-23T11:28:32Z")

</div>

Your latest method is nearly there but is a bit clumsy. You should think about using string formatting:`ls_querystring = """
UPDATE tempwheeldiameter
SET sensor1 = %f,
sensor2 = %f,
sensor3 = %f,
sensor4 = %f
WHERE WHEEL_RFID = '%s'
AND OPERATORCODE = '%s'
AND RADIALLOCATION = '%s'
AND WHEELMODEL = '%s'
""" % (ln_sensor1, ln_sensor2, ln_sensor3, ln_sensor4, ls_rfid, ls_operatorcode, str(slidervalue), ls_assembly)`Note the single quotes around string values. The triple quotes allow you to enter a string across multiple lines.

---

<div class="post-metadata">

**Author:** ![anna](https://avatars.discourse-cdn.com/v4/letter/a/779978/32.png) [@anna](https://forum.inductiveautomation.com/u/anna)\
**Post date:** [March 25, 2013, 5:42pm UTC](https://forum.inductiveautomation.com/t/database-update-syntax/6089/3 "2013-03-25T17:42:40Z")

</div>

Here are the Manual pages with examples:  
[http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system\_db\_runupdatequery.htm](http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system_db_runupdatequery.htm)  
[http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system\_db\_runprepupdate.htm](http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system_db_runprepupdate.htm)