# Using update, Insert and select query in a single named query on same database **URL:** \ **Category:** Ignition\ **Tags:** perspective, namedqueries\ **Created:** [December 4, 2024, 7:39am UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402 "2024-12-04T07:39:38Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Amitkumar\_Ravat1](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Amitkumar\_Ravat1](https://forum.inductiveautomation.com/u/Amitkumar_Ravat1)\ **Post date:** [December 4, 2024, 7:39am UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/1 "2024-12-04T07:39:38Z")
Can I use update, Insert and select query in a single named query on same database with query type as Query not Update type ? Insert into dbo.TableToUpdate(field1, field2) values (value1, value2) UPDATE dbo.TableToUpdate SET Field1 = value SELECT \* FROM dbo.TableToUpdate ---
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [December 4, 2024, 8:41am UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/2 "2024-12-04T08:41:43Z")
I think you can't, not really sure. However, you should separate the named queries or try to combine actions. What about: ```python INSERT INTO dbo.TableToUpdate( field1, field2 ) VALUES ( value, value2 ) ``` It doesn't make much sense to write a value and then instantly changing it. If you could explain a bit more your problem maybe there is another viable solution for you. ---
**Author:** ![Amitkumar\_Ravat1](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Amitkumar\_Ravat1](https://forum.inductiveautomation.com/u/Amitkumar_Ravat1)\ **Post date:** [December 4, 2024, 8:47am UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/3 "2024-12-04T08:47:55Z")
DECLARE @Var1 INT = 1, @Var2 INT = 374, @Var3 INT = 308 BEGIN -- SET NOCOUNT ON DECLARE @Var4 VARCHAR(20) = NULL; ``` -- DECLARE @TempTable1 with generic column names DECLARE @TempTable1 TABLE( Column1 VARCHAR(200), Column2 NVARCHAR(200), Column3 INT, Column4 VARCHAR(200), Column5 VARCHAR(500), Column6 INT, Column7 VARCHAR(200), Column8 VARCHAR(255), Column9 INT, Column10 INT, Column11 INT, Column12 VARCHAR(255), Column13 INT, Column14 NVARCHAR(200), Column15 DATETIME, Column16 INT ); -- INSERT data into @TempTable1 INSERT INTO @TempTable1 SELECT DISTINCT T1.Column1, ISNULL(T1.Column2, '') AS Column2, ROW_NUMBER() OVER (ORDER BY T1.Column2) AS Column3, T2.Column4, T2.Column5, T3.Column6, T3.Column7, T1.Column8, T4.Column9, T1.Column10, T1.Column11, T5.Column12, ISNULL(T1.Column13, '') AS Column13, T1.Column13, T6.Column14, T5.Column15, 0 AS Column16 FROM Table1 T1 WITH(NOLOCK) INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.Column10 = T2.Column10 INNER JOIN Table3 T3 WITH(NOLOCK) ON T3.Column6 = T1.Column6 INNER JOIN Table4 T4 WITH(NOLOCK) ON T4.Column10 = T2.Column10 INNER JOIN Table5 T5 WITH(NOLOCK) ON T5.Column2 = T1.Column2 WHERE T5.Column12 = 5 AND T1.Column11 = @Var1 AND T5.Column3 = @Var2 AND T4.Column9 = 1 ORDER BY T5.Column15; -- Delete records from @TempTable1 DELETE FROM @TempTable1 WHERE Column2 IN ( SELECT Column2 FROM @TempTable1 Temp1 INNER JOIN Table6 T6 WITH(NOLOCK) ON T6.Column2 = Temp1.Column2 WHERE T6.Column3 <> @Var2 ); -- DECLARE @TempTable2 with generic column names DECLARE @TempTable2 TABLE( Column1 VARCHAR(200), Column2 NVARCHAR(200), Column3 INT, Column4 VARCHAR(200), Column5 VARCHAR(500), Column6 INT, Column7 VARCHAR(200), Column8 VARCHAR(255), Column9 INT, Column10 INT, Column11 INT, Column12 VARCHAR(255), Column13 INT, Column14 NVARCHAR(200), Column15 DATETIME, Column16 INT ); -- Insert data into @TempTable2 from @TempTable1 INSERT INTO @TempTable2 SELECT * FROM @TempTable1 ORDER BY Column15; -- Update Column16 in @TempTable2 UPDATE TOP(1) @TempTable2 SET Column16 = 1; -- Update Column16 in the original table using placeholders UPDATE Temp SET Column16 = 2 FROM @TempTable2 Temp INNER JOIN Table5 T5 WITH(NOLOCK) ON T5.Column2 = Temp.Column2 WHERE T5.Column12 = 3 AND T5.Column3 = @Var3; -- Select the final result SELECT Column1, Column3, Column2, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14, Column15, Column16 FROM @TempTable2; ``` END; I am trying to use the above sample query ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [December 4, 2024, 8:57am UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/4 "2024-12-04T08:57:54Z")
See here: > [@MySQL Statement is not working](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/5): > > In general, JDBC doesn't support SQL scripts, just single SQL statements. If you want SQL that is widely compatible, don't use DECLARE, or BEGIN/END blocks, et cetera. Some JDBC drivers support scripts, and others do not. Don't use them, and don't assume something that works in a workbench will work in Ignition. ---
**Author:** ![Amitkumar\_Ravat1](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Amitkumar\_Ravat1](https://forum.inductiveautomation.com/u/Amitkumar_Ravat1)\ **Post date:** [December 4, 2024, 9:14am UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/5 "2024-12-04T09:14:31Z")
looks similar but what i did is created a store procedure and it called it from named query but gave error as time out few times and most of the time it works all of sudden it stops working. And how we can resolve the issue as of now we have switched to prepquery instead of namedquery as namedquery all of sudden stops working. ---
**Author:** ![Lynn\_Martineau](https://avatars.discourse-cdn.com/v4/letter/l/f1d935/32.png) [@Lynn\_Martineau](https://forum.inductiveautomation.com/u/Lynn_Martineau)\ **Post date:** [December 4, 2024, 2:00pm UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/6 "2024-12-04T14:00:11Z")
Take a look at the MERGE command for your database. e.g. [SQL Server MERGE](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16) That will give you either INSERT or UPDATE but take a look at the reference posted by @Transistor; With NamedQueries, you need to choose between `Query` and `Update Query`; you won't be able to cross that line. As a personal preference, I create folders (SELECT, INSERT, UPDATE, DELETE) and create a NamedQuery of the table name I'm working with inside each folder. That way, calling a NamedQuery it feels more natural and SQL like as well as providing a little self-documentation of what you are doing when you call: - SELECT/TableName - INSERT/TableName - UPDATE/TableName ---
**Author:** ![Amitkumar\_Ravat1](https://avatars.discourse-cdn.com/v4/letter/a/b5a626/32.png) [@Amitkumar\_Ravat1](https://forum.inductiveautomation.com/u/Amitkumar_Ravat1)\ **Post date:** [December 4, 2024, 2:26pm UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/7 "2024-12-04T14:26:26Z")
As per Requirement and data I have we need to declare a table and then insert data into it and then parse data from it update and delete it. how to overcome such issue ---
**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:** [December 4, 2024, 2:46pm UTC](https://forum.inductiveautomation.com/t/using-update-insert-and-select-query-in-a-single-named-query-on-same-database/99402/8 "2024-12-04T14:46:32Z")
A jython script can do everything you are asking for. Look at the user manual for system.db.\* functions