Scripting update query using dataset

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.

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?

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.

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

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

Something like this:

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

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

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