# SQL update table **URL:** \ **Category:** Ignition\ **Created:** [May 15, 2017, 2:45pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300 "2017-05-15T14:45:33Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\ **Post date:** [May 15, 2017, 2:45pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/1 "2017-05-15T14:45:33Z")
Hi, I have a data set pulled into ignition tag that updates every 24hrs( I don’t have permission to access that DB and this is the only way I can get that dataset). I would like to save the dataset into my DB and update it every 24hrs to reflect the changes that occurred in the last 24 hrs, what would be the best way to do such thing? I was thinking maybe a procedure that will delete the old table and write the new data, perhaps there is a better way? Oh the reason I want that data in my DB is to be able to query it and search it as needed, I don’t have the ability to do that with the original source ---
**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\ **Post date:** [May 15, 2017, 3:24pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/2 "2017-05-15T15:24:36Z")
How is the data set getting pulled into your tag? You could write a Value Changed Script on the tag that gets updated every 24 hours. More information here: [https://docs.inductiveautomation.com/display/DOC79/Tag+Event+Scripts](https://docs.inductiveautomation.com/display/DOC79/Tag+Event+Scripts) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 15, 2017, 3:24pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/3 "2017-05-15T15:24:46Z")
Two approaches: 1. Move the data into your own database. Use a tag event script to catch the update every 24 hours and script a single transaction that truncates your copy then inserts all of the new records. Provides full access to your database’s query engine for any further actions. Definitely do this if you wish to use joins with other data. 2. Use the [view() expression function](https://www.automation-pros.com/simaids/doc/expression.view.html) from the [Simulation Aids add-on module](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) (free) to search/compute directly from your existing dataset into new datasets using a Pseudo-SQL query. ---
**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\ **Post date:** [May 15, 2017, 5:43pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/4 "2017-05-15T17:43:38Z")
I like the 2nd option best, I have added your plugin but unsure of the syntax. say I wanted to SELECT TOP 1 from the dataset,I created an expression tag with the following code: > view("SELECT TOP 1",{[NoProvider]StoreRoomInventory}) \*"NoProvider" is my provider name. ---
**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\ **Post date:** [May 15, 2017, 5:45pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/5 "2017-05-15T17:45:27Z")
Its from an SQL Query, I Can’t query that DB every time I want execute a new search. The 2nd repose below is more inline of what I need. Thank you for you reply. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 15, 2017, 8:13pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/6 "2017-05-15T20:13:49Z")
The Pseudo-SQL has a strict format – no “TOP n” or “LIMIT n” clause. But you can use a where clause with the row index to get this behavior: ```auto view("SELECT * WHERE _r==0", {[NoProvider]StoreRoomInventory}) ``` However, for this particular case, where you know the row you wish to return, I would use the faster objectScript() expression function to manually construct a dataset with that row. Like so: ```auto objectScript("system.dataset.toDataSet(args[0].columnNames, [[args[0].getValueAt(0, c) for c in range(args[0].columnCount]])", {[NoProvider]StoreRoomInventory}) ``` ---
**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\ **Post date:** [May 15, 2017, 9:02pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/7 "2017-05-15T21:02:44Z")
How would I go about using column names in a query such as this. > SELECT \* > FROM StoreRoomInventory > WHERE > ItemDesc LIKE '%{Root Container.Text Field 2.text}%' AND > ItemDesc LIKE '%{Root Container.Text Field 11.text}%' OR > JDENumber LIKE '%{Root Container.Text Field 2.text}%' > ORDER BY LastMoveDate DESC I don't follow what pyExpression and how the args would work here. > Select > pyExpression, > pyExpression As ColumnName, > pyExpression As "Column Name" ... > Where pyConditionalExpression > Group By pyExpression, pyExpression ... > Pivot pyExpression For pyStringExpression [In pySequenceExpression] > Having pyConditionalExpression > Order By pyExpression, pyExpression ... ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 15, 2017, 10:58pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/8 "2017-05-15T22:58:28Z")
Something like this, as long as LastMoveDate really is a date/time datatype in the source: ```auto view("SELECT * WHERE args[0] in ItemDesc and args[1] in ItemDesc or args[0] in JDENumber ORDER BY -LastMoveDate.time", {[NoProvider]StoreRoomInventory}, {Root Container.Text Field 2.text}, {Root Container.Text Field 11.text}) ``` Note that text field 2 is passed as a single parameter and referenced twice. I’m not sure whether you’ll need parentheses in your where clause: “(x and y) or z” versus “x and (y or z)”. Note that java “Date” datatypes have a “time” property that is milliseconds since the Unix epoch, which can be conveniently negated in python to make a descending order. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 15, 2017, 11:04pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/9 "2017-05-15T23:04:25Z")
When working with strings in python, [this method reference](http://www.jython.org/docs/library/stdtypes.html#sequence-types-str-unicode-list-tuple-buffer-xrange) is a big help. ---
**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\ **Post date:** [July 10, 2017, 7:13pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/10 "2017-07-10T19:13:05Z")
So now that I have this working, is there a way to do a join between a sql query and the Pseudo sql query? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 10, 2017, 9:06pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/11 "2017-07-10T21:06:36Z")
Unfortunately, no. I’ve thought about ways to do it, but it is rather daunting. /-: ---
**Author:** ![Jado58](https://avatars.discourse-cdn.com/v4/letter/j/cdc98d/32.png) [@Jado58](https://forum.inductiveautomation.com/u/Jado58)\ **Post date:** [July 14, 2017, 4:06pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/12 "2017-07-14T16:06:37Z")
So i can get around using the join statement by using using a Custom propriety containing a dataset(1 column), would I be able too look for matching values in my dataset instead if matching one value at a time? `view("SELECT * where args[0] in ItemNumber " ,{StoreRoomInventory},{Root Container.Power Table.data})` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 15, 2017, 5:21pm UTC](https://forum.inductiveautomation.com/t/sql-update-table/14300/13 "2017-07-15T17:21:39Z")
You’d have to flip your expression around a bit, and unwrap the 2nd dataset into a list, since the where clause must use python syntax. Something like this: ```auto view("Select * Where ItemNumber in [x[0] for x in system.dataset.toPyDataset(args[0])]", {StoreRoomInventory},{Root Container.Power Table.data}) ``` Unfortunately, the dataset =\> list conversion will happen for every row in your inventory, as there’s no understanding of pure functions in the Pseudo-SQL parser. But, you can optimize this after setting it up by using the Debug logger to show the generated jython, move the ds=\>list conversion ahead of the main loop, and put the new code in a project script function called from objectScript() instead.