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

Ignition
1

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.

2

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

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.

3

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!

4

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

You should be able to add the WHERE in there.

5

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

6

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

7 
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)