# Is it possible to update a row in an SQL table with values from a tag dataset?

**URL:** <https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [April 19, 2023, 7:50pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667 "2023-04-19T19:50:31Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![AVG04](https://avatars.discourse-cdn.com/v4/letter/a/cab0a1/32.png) [@AVG04](https://forum.inductiveautomation.com/u/AVG04)\
**Post date:** [April 19, 2023, 7:50pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/1 "2023-04-19T19:50:31Z")

</div>

a rookie here. would like to get your inputs on my current situation:

I currently have a tag dataset that is identical to a table on the SQL [1Row x 30 Columns].

tag dataset is updated with the modifications made on its properties and I want to update the SQL table after pressing a button on the ignition.

I would like to know if there is a better way of updating the SQL table than using the below query?

> UPDATE mytable SET column1 = 'new value 1', column2 = 'new value 2', column3 = 'new value 3', ..., column16 = 'new value 16' WHERE id = 1;

Doing this makes me think that it is better to use a UDT tag than a tag dataset.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [April 19, 2023, 7:57pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/2 "2023-04-19T19:57:57Z")

</div>

Are you aware of `runPrepUpdate`?  
[https://docs.inductiveautomation.com/display/DOC81/Inserting+Data+into+a+Database](https://docs.inductiveautomation.com/display/DOC81/Inserting+Data+into+a+Database)

```auto
query = "INSERT INTO table (col1, col2, col3) VALUES (?,?,?)"
args = [value1, value2, value3]
system.db.runPrepUpdate(query, args)

```

* * *

Tip: for nicely formatted code in your forum posts use the \</\> button.

---

<div class="post-metadata">

**Author:** ![AVG04](https://avatars.discourse-cdn.com/v4/letter/a/cab0a1/32.png) [@AVG04](https://forum.inductiveautomation.com/u/AVG04)\
**Post date:** [April 19, 2023, 8:24pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/3 "2023-04-19T20:24:00Z")

</div>

I did looked into that, but what I want to do is to replace the values in an existing row (Row 1). I do think I need a WHERE condition for my query.

The SQL table will just function as a temporary data storage and the data will be deleted and replaced after performing some ignition tasks.

Thanks for the tip!

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [April 19, 2023, 8:27pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/4 "2023-04-19T20:27:26Z")

</div>

Oops. I forgot what the question was. There's this too:  
[https://docs.inductiveautomation.com/display/DOC81/system.db.runUpdateQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runUpdateQuery)

You should be able to add the WHERE in there.

---

<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:** [April 19, 2023, 8:41pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/5 "2023-04-19T20:41:24Z")

</div>

You should be able to use a WHERE in _runPrepUpdate()_, as well

---

<div class="post-metadata">

**Author:** ![AVG04](https://avatars.discourse-cdn.com/v4/letter/a/cab0a1/32.png) [@AVG04](https://forum.inductiveautomation.com/u/AVG04)\
**Post date:** [April 20, 2023, 10:38am UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/6 "2023-04-20T10:38:40Z")

</div>

Thanks for your inputs! Will check what works best for my setup.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [April 21, 2023, 9:29am UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-update-a-row-in-an-sql-table-with-values-from-a-tag-dataset/72667/7 "2023-04-21T09:29:19Z")

</div>

```python
from itertools import chain

def update_row(table, row_id, **args):
	marks = ', '.join("?=?" for _ in args)
	values = list(chain.from_iterable(args.items()))
	q = "update {} set {} where id = {}".format(table, marks, row_id)
	
	return system.db.runPrepUpdate(q, values)
	

data = {
	'column_name': value,
	'foo': 42,
	'bar': 21,
	'pox': 12
}

update_row('table', row_id, **data)
# or
update_row('table', row_id, column_name=value, foo=42, bar=21, pox=12)

```