# Help with SQL query **URL:** \ **Category:** Ignition\ **Created:** [February 5, 2014, 10:31pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963 "2014-02-05T22:31:36Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 5, 2014, 10:31pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/1 "2014-02-05T22:31:36Z")
I have a transaction group transferring 600 values from a PLC DINT array to a table called “valve\_timeout”. Using "SELECT \* FROM valve\_timeout " I have populated a table with all 600 results. But I only want to display results that are greater than 3. My tags are in the format “Valve\_Timeout\_0\_” to “Valve\_Timeout\_599\_”. Thanks in advance. ---
**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\ **Post date:** [February 6, 2014, 12:48am UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/2 "2014-02-06T00:48:48Z")
Im not quite sure what you are looking for and need to know what columns are in your table. if you are trying to pull out just the tag values that are greater than 3 try this. replace “value” with whatever the column name is that holds your values. select \* from valve\_timeout where value \> 3 if you are looking for somethign different let us know. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2014, 2:15pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/3 "2014-02-06T14:15:56Z")
This is what my SQL table contains: I have got the results table in ignition to sort of work (for 1 column only) by using: “SELECT Valve\_Timeout\_1\_ FROM valve\_timeout WHERE valve\_timeout\_1\_ \> 3” Based on the data you can see in my 1st image above, I would like to display results from any column (where value inside column \> 3) so that my results table looks something like the results below: ---
**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\ **Post date:** [February 6, 2014, 2:52pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/4 "2014-02-06T14:52:11Z")
I didnt realize you had a column for each tag. you should probably change that to be a row for each tag which will make querying easier. Someone else may be able to help you make what you have work though. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2014, 2:59pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/5 "2014-02-06T14:59:13Z")
OK Thanks. Is there a setting in transaction groups that I can change to apply it to rows instead of columns? ---
**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\ **Post date:** [February 6, 2014, 3:08pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/6 "2014-02-06T15:08:59Z")
you would create a new block group. setup your items in order as a block item from 0-600. Make sure you select the option to store row id. if you are storing history to this table then use insert new block under ‘Table action’ and also store your block id. If you are just wanting the latest values I believe you would use update/select the first block. You may want to read up on the block groups as I may be incorrect. its my first time really looking at it. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2014, 3:47pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/7 "2014-02-06T15:47:11Z")
OK I think it’s pretty much working. I set the table action to “insert changed rows”. My SQL query is: “SELECT row\_id, Valve\_Timeout\_0\_, t\_stamp FROM valve\_timeout WHERE valve\_timeout\_0\_ \> 3” This is the result: Now next question. It won’t let me execute the sql query “DELTEE FROM valve\_timeout”. Is there anyway to clear all my data (with a separate button)? Thanks. ---
**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\ **Post date:** [February 6, 2014, 4:07pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/8 "2014-02-06T16:07:24Z")
You can do this to add your Tagpath part to the rowid “SELECT ‘Valve\_Timeout\_’ + cast(row\_id as varchar), Valve\_Timeout\_0\_, t\_stamp FROM valve\_timeout WHERE valve\_timeout\_0\_ \> 3” “DELTEE FROM valve\_timeout” You misspelled delete ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2014, 4:24pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/9 "2014-02-06T16:24:41Z")
Oops yes I mis-spelt on the forum, when I enter it spelt correctly I get this error: ```auto Exception: Error running query: SQLQuery(query=delete from valve_timeout, database=MySQL)@5000ms On: F4 Engineering.Root Container.Button 1.text caused by GatewayException: Can not issue data manipulation statements with executeQuery(). caused by SQLException: Can not issue data manipulation statements with executeQuery(). Ignition v7.6.3 (b2013090513) Java: Sun Microsystems Inc. 1.6.0_31 ``` ---
**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\ **Post date:** [February 6, 2014, 4:29pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/10 "2014-02-06T16:29:15Z")
post the code that you have on the pushbutton. ---
**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\ **Post date:** [February 6, 2014, 4:33pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/11 "2014-02-06T16:33:47Z")
For DELETE, you probably need to use [system.db.runUpdateQuery](http://www.inductiveautomation.com/support/usermanuals/ignition/system_db_runupdatequery.htm) instead of [system.db.runQuery](http://www.inductiveautomation.com/support/usermanuals/ignition/system_db_runquery.htm). ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2014, 4:38pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/12 "2014-02-06T16:38:26Z")
It’s simply “DELETE FROM valve\_timeout”… Do I need a ‘WHERE’ clause to make it work? I can execute it in MySQL workbench ok without a WHERE. Does the “Can not issue data manipulation statements with executeQuery” detail in the error message mean Ignition can’t execute delete statements? Adam, do you mean enter the query into the “update query” field in the property binding? Or it is entered somewhere else… ---
**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\ **Post date:** [February 6, 2014, 4:49pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/13 "2014-02-06T16:49:56Z")
How are you executing the DELETE command? As diat150 suggests, post your code, please. ---
**Author:** ![anna](https://avatars.discourse-cdn.com/v4/letter/a/779978/32.png) [@anna](https://forum.inductiveautomation.com/u/anna)\ **Post date:** [February 6, 2014, 7:10pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/14 "2014-02-06T19:10:05Z")
What function you use is of essence (that’s why we want to see the code). Check out the following descriptions from the User Manual, for example: [quote]system.db.runQuery Description Runs a SQL query, usually a SELECT query, against a database… [/quote] [quote]system.db.runUpdateQuery Description Runs a query against a database connection, returning the number of rows affected. Typically this is an UPDATE, INSERT, or DELETE query…[/quote] If you are taking user input that is used in the query, the “Prep” version is better as it sanitizes user input. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2014, 8:31pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-query/7963/15 "2014-02-06T20:31:52Z")
OK I have it working, I was entering my code in the wrong place earlier. Now using: ```auto system.db.runUpdateQuery("DELETE FROM valve_timeout") ``` in the correct place and it’s working. Thanks again.