# Cell update on a pivoted table **URL:** \ **Category:** Ignition\ **Created:** [November 10, 2025, 8:40pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076 "2025-11-10T20:40:11Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Mike\_Fowler](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@Mike\_Fowler](https://forum.inductiveautomation.com/u/Mike_Fowler)\ **Post date:** [November 10, 2025, 8:40pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/1 "2025-11-10T20:40:11Z")
I’m having trouble updating a data table when a cell is edited. The mysql table looks like this. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/5/950f6a4a49375a36cc13124d9d90e46391e7d187.png) I want to pivot the table to look like this ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/b/6bc91d872588155f4b378a71f896d826f72e4eda.png) Using this query with a bunch of unions `SELECT "Number" Description, Recipe_Number` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` `Union All` `SELECT "Product" Description, Product_Code` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` `Union All` `SELECT "Grist Weight To Add" Description, Grist_Weight_To_Add` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` `Union All` `SELECT "Strike vol" Description, Strike_Water_to_Add` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` `Union All` `SELECT "Ramp Soak Cycles" Description, Samp_Soak_Cycles` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` `Union All` `SELECT "Ramp Soak Time 1" Description, Mix_Tank_Soak_Time_1` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` `Union All` `SELECT "Ramp Soak Temp 1" Description, Mix_Tank_Temperature_1` `FROM pilotrecipe` `Where Recipe_Number Like {Root Container.CurrentRecipe}` This update script works just fine on a “select All” type of a table. It does not function on my pivoted version. Maybe something to do with changing Column names into a single row? ```python row = event.row col = event.column colName = event.source.data.getColumnName(col) Recipe_Number=event.source.data.getValueAt(row,0) value = event.newValue query = "Update pilotrecipe SET %s = ? WHERE Recipe_Number = ?" system.db.runPrepUpdate(query % colName, [value, Recipe_Number], 'history') ``` Is there a more effective way to pivot this table and allow cell updates, or does my update scrip need to evolve? ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 10, 2025, 8:45pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/2 "2025-11-10T20:45:17Z")
Welcome, Mike. That query needs some TLC, edit your post to change the query and script into code blocks. This way we can easily copy and paste them to help you out. [Wiki - how to post code on this forum - General Discussion - Inductive Automation Forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831) ---
**Author:** ![Mike\_Fowler](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@Mike\_Fowler](https://forum.inductiveautomation.com/u/Mike_Fowler)\ **Post date:** [November 10, 2025, 9:17pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/3 "2025-11-10T21:17:39Z")
Good call. Thank you. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 10, 2025, 9:31pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/4 "2025-11-10T21:31:23Z")
Nothing wrong with the query, technically, I missed the pivot part. Your problem seems to be when you get the `Recipe_Number`. You are getting the first value in which ever row is edited. Instead, with the pivot, you _always_ want to get the second value of the first row. Try this, ```python Recipe_Number=event.source.data.getValueAt(0,'Recipe') ``` ---
**Author:** ![Mike\_Fowler](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@Mike\_Fowler](https://forum.inductiveautomation.com/u/Mike_Fowler)\ **Post date:** [November 10, 2025, 9:51pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/5 "2025-11-10T21:51:02Z")
`Recipe_Number=event.source.data.getValueAt(0,'Recipe')` Gives me an error for Column 'Recipe' doesn't exist in this dataset. `Recipe_Number=event.source.data.getValueAt(0,'Recipe_Number')` Trying this changes the recipe number instead of the intended cell ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 10, 2025, 10:15pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/6 "2025-11-10T22:15:35Z")
Add `Recipe` after your selected values in the query like you did `Description`. For the pivot, it should be more like this ```python row = event.row # if you don't want to allow the recipe number to be edited if row > 0: # the field_name is not the column name but the first value in the edited row field_name = event.source.data.getValueAt(row,0) # recipe number is always second value in the first row recipe_number = event.source.data.getValueAt(0,'Recipe') # or use 1 instead of Recipe value = event.newValue query = "Update pilotrecipe SET %s = ? WHERE Recipe_Number = ?" system.db.runPrepUpdate(query % field_name, [value, recipe_number], 'history') ``` Try checking your values too. ```python system.perspective.print(query % colName) ``` ---
**Author:** ![Mike\_Fowler](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@Mike\_Fowler](https://forum.inductiveautomation.com/u/Mike_Fowler)\ **Post date:** [November 10, 2025, 11:20pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/7 "2025-11-10T23:20:51Z")
```python caused by Exception: Error executing system.db.runPrepUpdate(Update pilotrecipe SET Ramp Soak Cycles = ? WHERE Recipe_Number = ?, [4, 1], history, , false, false) caused by GatewayException: SQL error for "Update pilotrecipe SET Ramp Soak Cycles = ? WHERE Recipe_Number = ?": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Soak Cycles = '4' WHERE Recipe_Number = '1'' at line 1 caused by SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Soak Cycles = '4' WHERE Recipe_Number = '1'' at line 1 ``` I’ve added Recipe to the original query and used your update script. Thank you dk for the help. I’m in the weeds on this one. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 11, 2025, 12:18am UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/8 "2025-11-11T00:18:12Z")
I think it’s the spaces, it doesn't match the table column names in the db. Keep the underscores in your query, or use `replace` to swap spaces for underscores. ---
**Author:** ![Mike\_Fowler](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@Mike\_Fowler](https://forum.inductiveautomation.com/u/Mike_Fowler)\ **Post date:** [November 12, 2025, 4:36pm UTC](https://forum.inductiveautomation.com/t/cell-update-on-a-pivoted-table/112076/9 "2025-11-12T16:36:53Z")
Thank you dk. Adding the underscores did the trick. I’ll see if I can remove them using ‘replace’ in the query.