# \[BUG-6572\]Updating last row of table to tags using TX Group **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [August 18, 2022, 10:55pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182 "2022-08-18T22:55:22Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [August 18, 2022, 10:55pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/1 "2022-08-18T22:55:22Z")
I have a database table that is always changing, rows are being inserted, and sometimes rows are being updated. I am interested in bringing out the values for the **LAST ROW** of the table into Ignition memory tags. I started using a Standard Transaction group with the following configuration: - mode set to DB-\>OPC. - timer set to 1s - use custom index column = “rcdID” (the primary key of the table) - update/select = last - Only evaluate when values have changed = TRUE. Watch ALL tags. Everything works sweet if i link each column to its corresponding memory tag, **except** when trying to update the value of the index column, “rcdID”. If i try to link this column as shown below, i get an execution group error. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/c/cc03c35d5edb8d3406852b007b1375bbd249eb04.png) `Error during group execution. com.microsoft.sqlserver.jdbc.SQLServerException: Ambiguous column name 'RcdID'. Ambiguous column name 'RcdID'.` Why can we not also read this column, while using it in the where query for the transaction group? ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [August 19, 2022, 11:47am UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/2 "2022-08-19T11:47:53Z")
> [@richardNZ15](#): > > com.microsoft.sqlserver.jdbc.SQLServerException: Ambiguous column name 'RcdID'. Ambiguous column name 'RcdID' RcdID can be either a tag name or a db column name. It doesn't know which to use. ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [August 20, 2022, 12:47am UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/3 "2022-08-20T00:47:28Z")
The item name is “tag\_rcdID”, so shouldn’t that make it distinct from the db column name? However the actual tag path is: `[Corporate]Lumber/ERP/latestTagMasterRecord/rcdID` Would changing that memory tag to something slightly different fix it? ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [August 24, 2022, 11:20pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/4 "2022-08-24T23:20:40Z")
Changing the item name (in the TX Group), nor changing the tag name (realtime provider) did anything to help this. I still don’t understand the ambiguity here. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [August 24, 2022, 11:28pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/5 "2022-08-24T23:28:57Z")
What does your database table look like? This error about ambiguous column names is simply being relayed to you from the database via JDBC, it’s not originating in Ignition. ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [August 24, 2022, 11:35pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/6 "2022-08-24T23:35:58Z")
Create syntax: ```auto CREATE TABLE [dbo].[mesImportMaster]( [rcdID] [bigint] IDENTITY(1,1) NOT NULL, [tagID] [varchar](100) NULL, [tagSourceID] [varchar](100) NULL, [tagSourceDate] [datetime] NULL, [tagLocationID] [varchar](100) NULL, [tagThickID] [varchar](100) NULL, [tagWidthID] [varchar](100) NULL, [tagLengthID] [varchar](100) NULL, [tagGradeID] [varchar](100) NULL, [tagUFTwo] [varchar](100) NULL, [tagFBM] [varchar](100) NULL, [tagPieces] [int] NULL, [tagTally] [int] NULL, [tagLineSeq] [varchar](100) NULL, [tagImported] [varchar](10) NULL, [tagReceiving] [varchar](100) NULL, [tagPrintLabel] [varchar](100) NULL, [tagPrinted] [bit] NULL, [tagMachineID] [varchar](100) NULL, [tagProductID] [varchar](100) NULL, [tagImportDate] [datetime] NULL, [tagSurfacingID] [varchar](100) NULL, [tagStateID] [varchar](100) NULL, [tagVolume] [real] NULL, [dateModified] [datetime] NULL, [modifiedBy] [varchar](50) NULL, CONSTRAINT [PK_mesImportMaster] PRIMARY KEY CLUSTERED ( [rcdID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ``` I basically want the Transaction group to give me the last value in the table, for specific columns i’ve listed in the transaction group. This works fine for any column, except the column used as the primary key, **rcdID** I presume this is an issue because Ignition has to run a query that uses **ORDER BY rcdID DESC LIMIT 1** in the SQL statement but i don’t see how this should be an issue. ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 7, 2022, 8:30pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/7 "2022-09-07T20:30:21Z")
Is this possible? ---
**Author:** ![ggross](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ggross/32/12668_2.png) [@ggross](https://forum.inductiveautomation.com/u/ggross)\ **Post date:** [September 8, 2022, 5:44pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/8 "2022-09-08T17:44:15Z")
So what looks to be happening is Ignition is sending the following query to your SQL Server instance: ```auto SELECT "rcdID","tagLocationID","tagSourceID","tagID","tagImported","tagLengthID","rcdID" FROM mesImportMaster ORDER BY "rcdID" DESC ``` What looks to be happening is the ORDER BY statement is telling the DB to sort by a column that is non-unique. This causes SQLServer (and Oracle) to throw an error. MySQL and Postgres handle it without issue for some reason. At this point it doesn’t look like there is a work around for this and it is something we would need to make a change to the code base to work around and I am filing a bug for it. You might be able to create a trigger against the DB to clone the **rcdID** column to another column as a work around. Garth ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 8, 2022, 8:31pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/9 "2022-09-08T20:31:29Z")
Hi Garth, thanks for the actual query, that was what i was trying to find out about. Why does it try to SELECT “rcdID” twice though? Is that because “rcdID” is used as the “Custom index column” option, and Ignition just adds whatever that is to the start/end of the query? ---
**Author:** ![ggross](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ggross/32/12668_2.png) [@ggross](https://forum.inductiveautomation.com/u/ggross)\ **Post date:** [September 9, 2022, 3:46pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/10 "2022-09-09T15:46:07Z")
> [@richardNZ15](#): > > Why does it try to SELECT "rcdID" twice though? > Is that because "rcdID" is used as the "Custom index column" option, and Ignition just adds whatever that is to the start/end of the query? Yeah, Ignition is always including the index when doing a Transaction Group select statement (I am guessing to ensure the query returns as quick as possible as well as to quickly find the last entry) along with all the columns you are monitoring. The default state for Transaction Groups is for the TG to generate its own index column and it isn't really available to access within the group itself, and this specific scenario looks to be something we missed when this was developed years ago. Garth ---
**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:** [September 9, 2022, 4:00pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/11 "2022-09-09T16:00:05Z")
_Pssst!_ Anything a transaction group does can also be done by a timer or scheduled event script… ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [September 9, 2022, 9:01pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/12 "2022-09-09T21:01:57Z")
Would creating a view in the database instead work in this case? Create the view as a SELECT TOP 1 \* ORDER BY rcdID DESC That way the newest record will always be there. (I would test but I am not around a gateway at the moment) ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 14, 2022, 12:09am UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/13 "2022-09-14T00:09:21Z")
Potentially, I’ll have a play with that idea. ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 14, 2022, 12:10am UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/14 "2022-09-14T00:10:37Z")
I suppose, but not as good at detecting ANY change to ANY column in the TX Group, which is what i am trying to achieve. Also not as intuitive to other engineers that may come after me. I like the simplicity of Transaction Groups for the less advanced users. ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 22, 2022, 9:24pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/15 "2022-09-22T21:24:15Z")
Tried this just now. Used the VIEW in the Transaction Group table, but still get the ambiguous column name ‘rcdID’ error. Damn. ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [September 22, 2022, 9:32pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/16 "2022-09-22T21:32:54Z")
Perhaps you can clear the error by prefacing the column name with the table name, like you would if you were combining two different tables: Select mesImportMaster.rcdID… ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 22, 2022, 10:48pm UTC](https://forum.inductiveautomation.com/t/bug-6572-updating-last-row-of-table-to-tags-using-tx-group/63182/17 "2022-09-22T22:48:57Z")
Good tip, i took a slightly different approach and got it working… In my VIEW definition i simply added another column referencing the rcdID but giving it an alias name. ie: `SELECT TOP (1) rcdID, rcdID AS 'rcdID_Latest', tagID ...` Then the Transaction Group tag that i want to write to my Ignition memory tag can reference the rcdID\_Latest column, and the TX Group itself can still use the original rcdID column for its “custom column” and ORDER BY clauses without conflict… Thanks for all the tips, glad i finally got it working, and understand how these groups work behind the scenes a bit more.