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?