# Scripting update query using dataset

**URL:** <https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709>\
**Category:** Ignition\
**Created:** [July 3, 2018, 2:53pm UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709 "2018-07-03T14:53:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![waker1der](https://avatars.discourse-cdn.com/v4/letter/w/dbc845/32.png) [@waker1der](https://forum.inductiveautomation.com/u/waker1der)\
**Post date:** [July 3, 2018, 2:53pm UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709/1 "2018-07-03T14:53:18Z")

</div>

I am building a dashboard so that managers can see current issues across multiple sites. this means querying data from one datawarehouse (only read access allowed) and then updating a dedicated table that i own. my db type does not support stored procedures so i cam attempting to achieve this with a project script that will update every 2 hours.

i can run the select query and write to a memory tag that is a data set. my problem is how to write the update query as my only experience is updating several rows in one column. My select query has 23 columns so i have a 10Rx23C dataset.

```auto
getFromTable = "SELECT create_date,case_id,..............,site,status,time_spent,type FROM table.name WHERE initial_escalation_time >= current_date-2 AND min_impact <= 2 ORDER BY site, create_date DESC;"

tableData = system.db.runPrepQuery(getFromTable) tableDataSet = system.dataset.toPyDataSet(bookerData)

system.tag.write("FC Metrics/selectFromTable",tableData)

```

(i’ve removed all 23 columns for ease)

how would i construct the UPDATE query so that each column is updated using the data in tableDataSet?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 3, 2018, 3:10pm UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709/2 "2018-07-03T15:10:36Z")

</div>

Generally you have to loop through the SELECT result set executing UPDATE one row at a time into the target. If your DB architecture has the data warehouse set up as a remote for the DB you own, you might be able to use an `UPDATE ... JOIN ...` construct instead. That can’t be done across different DB connections.

---

<div class="post-metadata">

**Author:** ![waker1der](https://avatars.discourse-cdn.com/v4/letter/w/dbc845/32.png) [@waker1der](https://forum.inductiveautomation.com/u/waker1der)\
**Post date:** [July 3, 2018, 3:13pm UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709/3 "2018-07-03T15:13:59Z")

</div>

the tables are in different DB’s but i understand looping through result but im struggling to write it as an update

im guessing i would start

```auto
for rows in tableDataSet:
        UPDATE tablename SET ...... WHERE .....

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 3, 2018, 3:18pm UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709/4 "2018-07-03T15:18:41Z")

</div>

Something like this:

```auto
pyds = system.db.runPrepQuery(.....)
for row in pyds:
    system.db.runPrepUpdate(
        "Update mytable Set somevalue=?, ..... Where case_id=?",
        [row['somevalue'], ....., row['case_id']])

```

---

<div class="post-metadata">

**Author:** ![waker1der](https://avatars.discourse-cdn.com/v4/letter/w/dbc845/32.png) [@waker1der](https://forum.inductiveautomation.com/u/waker1der)\
**Post date:** [July 3, 2018, 3:49pm UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709/5 "2018-07-03T15:49:20Z")

</div>

thanks for the help i’ll work on that tonight and let you know how i get on

---

<div class="post-metadata">

**Author:** ![waker1der](https://avatars.discourse-cdn.com/v4/letter/w/dbc845/32.png) [@waker1der](https://forum.inductiveautomation.com/u/waker1der)\
**Post date:** [July 5, 2018, 10:19am UTC](https://forum.inductiveautomation.com/t/scripting-update-query-using-dataset/18709/6 "2018-07-05T10:19:35Z")

</div>

just to loop back, the script works but i need to manage null values in the update.

because in the data set the values are not assigned to keys as in a dictionary so that when the update trys to run i am try to update the table with 20 values in to 23 columns