# Increase part count in Sql table **URL:** \ **Category:** Ignition\ **Created:** [August 7, 2023, 12:33pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513 "2023-08-07T12:33:54Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Joshua\_Hoekstra](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@Joshua\_Hoekstra](https://forum.inductiveautomation.com/u/Joshua_Hoekstra)\ **Post date:** [August 7, 2023, 12:33pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/1 "2023-08-07T12:33:54Z")
I am looking to do an increase in to a part-built table in one my active orders. I can each order have a specific ID which will help me determine what order I am on but I'm curious the most efficient way of increasing the part count in the column ever time a good part comes of the production line. Thanks ---
**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:** [August 7, 2023, 12:41pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/2 "2023-08-07T12:41:22Z")
How many parts per minute / hour day / year do you make? Why would you not create a new record with a timestamp for each? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 7, 2023, 12:41pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/3 "2023-08-07T12:41:33Z")
An update query. But I am sure you knew that. What question are really asking? - The most performant way? - The best way to run the update when a new part is detected? - How to write an update query? What have you tried? Did it work? If so why is that not good enough? ---
**Author:** ![Joshua\_Hoekstra](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@Joshua\_Hoekstra](https://forum.inductiveautomation.com/u/Joshua_Hoekstra)\ **Post date:** [August 7, 2023, 12:47pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/4 "2023-08-07T12:47:00Z")
So, what i have is a scheduler software that puts a schedule in to a sql database then with the part number, orderID number, quantity ordered and total built of order. So, what I have in the PLC end is the order number and I increase the Total built until it hits the total ordered quantity then goes to the next order. I want to Increase on the sql side so when i run a query i can see where they are at in the order. ---
**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:** [August 7, 2023, 12:53pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/5 "2023-08-07T12:53:18Z")
It sounds as though you just need to read the current count from the PLC with an OPC tag read. If the PLC tag doesn't reset at the start of a batch then, 1. Read the PLC current count with a regular OPC tag. 2. Create an SQL tag to read the count at the end of the last batch. 3. Create an expression tag to calculate the difference between the two. Tag 3 gives you the current batch count. ---
**Author:** ![Joshua\_Hoekstra](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@Joshua\_Hoekstra](https://forum.inductiveautomation.com/u/Joshua_Hoekstra)\ **Post date:** [August 7, 2023, 12:58pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/6 "2023-08-07T12:58:13Z")
The Order ID will change at the end of each order, and the order count will reset to zero ---
**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:** [August 7, 2023, 1:00pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/7 "2023-08-07T13:00:52Z")
So why does the order count tag not give you the required data? Why put it in a table - unless you need to record it when the machine is powered off when everyone goes home and the batch isn't finished yet. ---
**Author:** ![Joshua\_Hoekstra](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@Joshua\_Hoekstra](https://forum.inductiveautomation.com/u/Joshua_Hoekstra)\ **Post date:** [August 7, 2023, 1:03pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/8 "2023-08-07T13:03:48Z")
That's exactly why it always seems like they shut the line down during a order so I just want another level of protection. We have had it where a download was done to the plc, and it all got wiped out. ---
**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:** [August 7, 2023, 1:12pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/9 "2023-08-07T13:12:18Z")
Then you just need an SQL table to store miscellaneous pieces of data such as this and write to it to update the record. I'd be inclined to do a new INSERT each time the count changes - if there aren't a ridiculous number. That would allow you to extract all sorts of useful information such as average rate, time taken per part, etc. [https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate?src=sidebar](https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate?src=sidebar) To get the lates count, create a named query and extract the last row. In MySQL it would be something like, ```auto SELECT t_stamp, count FROM production_log WHERE machineId = 'Machine 7' ORDER BY t_stamp DESC LIMIT 1 ``` ---
**Author:** ![Joshua\_Hoekstra](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@Joshua\_Hoekstra](https://forum.inductiveautomation.com/u/Joshua_Hoekstra)\ **Post date:** [August 7, 2023, 1:15pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/10 "2023-08-07T13:15:40Z")
I will try this thank you ---
**Author:** ![Joshua\_Hoekstra](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@Joshua\_Hoekstra](https://forum.inductiveautomation.com/u/Joshua_Hoekstra)\ **Post date:** [August 7, 2023, 1:50pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/11 "2023-08-07T13:50:43Z")
Select {[~]ActivePallet/Data/Pcode}, Built FROM View\_ActiveOrders WHERE TargetLineId = 50 Limit 1 This is what i am trying for my sql tag but it doesnt seem to work ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 7, 2023, 2:00pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/12 "2023-08-07T14:00:55Z")
I don't think that you can modify column names in a query tag like that, (I have never tried personally as not the best practice). I would expect your query to look something like: ```auto SELECT Built FROM View_ActiveOrders WHERE Pcode = {[~]ActivePallet/Data/Pcode} AND TargetLineId = 50 LIMIT 1 ``` Note that this is a scalar query. This is needed if the result that you are expecting is a single value. ---
**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:** [August 7, 2023, 2:04pm UTC](https://forum.inductiveautomation.com/t/increase-part-count-in-sql-table/77513/13 "2023-08-07T14:04:31Z")
> [@Joshua\_Hoekstra](#): > > ... but it doesnt seem to work. Please explain, 1. What database engine your system is using. (MySQL, MariaDB, MS SQL, PostreSQL, etc.) 2. Where you used this code. 3. What you expected. 4. What you got. Tip: ![Formatting code](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/0/70741738ea3cbbe8a2d249e1194c80035567335c.png)