# Ignition sql update issue

**URL:** <https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755>\
**Category:** Ignition\
**Tags:** database\
**Created:** [June 17, 2020, 2:12pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755 "2020-06-17T14:12:31Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [June 17, 2020, 2:12pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755/1 "2020-06-17T14:12:31Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/5/53d5f21e4fa2b5729ef02a3253a80629a89a45e2.png)  
i have create a job use ignition work with my company’s erp to print label for the production.  
i use the scanner to input the data upside picture number 1 **EHC800-375791-2020-06**  
Then the red number 2’s polling rate is off, if the scan input change , this value will use sql also change  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/5/508874c9b769fd75bfc6c46682131527fa5de4e4.png)

there are red number 3,4,5 that data is link to red number 2, **C00127024,** all of them polling is off

my problem is , most of the case, it will work perfect.  
But i have twice meet the problem , when i scan the data in number 1, the number 2 will change correctly, but number 3,4,5 will keep the previous data(almost 1000 cycle i will meet 1 accident)  
i assume the data refresh have some issue at that time, but how to avoid these kind of unexpected issue , if these sql actually not change  
(upload://cTEBKzMCFOKOfJkbDYwh3tuygZS.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 17, 2020, 2:27pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755/2 "2020-06-17T14:27:45Z")

</div>

Are each of those fields database queries? It would probably be more efficient (and robust) to take care if it through scripting.

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [June 17, 2020, 2:41pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755/3 "2020-06-17T14:41:29Z")

</div>

yes, they are database queries.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/5/563c00259b5e2720ff16d00971a0fc3ed1c7bc8d.png)  
Here is the current structure, i scan the data 1, in table 1, then i use sql query know 2, then i use sql query know 3,4,5 in table 2, all the data is in my ERP  
I have no idea how to use scripting to run the sql query, read the data from the ERP , Ignition have some function like system.data… for scripting??

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 17, 2020, 3:46pm UTC](https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755/4 "2020-06-17T15:46:25Z")

</div>

Read up on [system.db.runPrepQuery()](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepQuery)

Use a JOIN in your query to get data from both tables at the same time.

Example to get you started, using the propertyChange event of the scan text field:

```python
if event.propertyName == 'text':
	SerNum = event.newValue

	query = """SELECT Table1.col1, Table1.col2, Table2.col3, Table2.col4, Table2.col5
			   FROM Table1
			   INNER JOIN Table2 ON Table1.col2=Table2.col2	
			   WHERE Table1.col1 = ?
			"""

	result = system.db.runPrepQuery(query, [SerNum], 'dbConnectionName')

	# In this example 'col1', etc. correlates to the columns in the query.
	value1 = row[0]['col1']
	value2 = row[0]['col2']
	value3 = row[0]['col3']
	value4 = row[0]['col4']
	value5 = row[0]['col5']

```

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [June 18, 2020, 3:30am UTC](https://forum.inductiveautomation.com/t/ignition-sql-update-issue/35755/5 "2020-06-18T03:30:52Z")

</div>

> [@JordanCClark](#):
>
> value1 = row[0]['col1'] value2 = row[0]['col2'] value3 = row[0]['col3'] value4 = row[0]['col4'] value5 = row[0]['col5']

it works, thanks a lot.