# SQL statement inserting record twice **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [August 8, 2023, 1:10pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567 "2023-08-08T13:10:03Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 1:10pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/1 "2023-08-08T13:10:03Z")
This is my code. Everything works as intended except for the "system.db.runNamedQuery('M5Waffles/addM5COMP')" which is supposed to take values from a table and place them in another table, but it does it twice. This query is only in one section so I know I'm not calling it twice. I'm thinking maybe it's because it's in a loop? The query for system.db.runNamedQuery('M5Waffles/updateM5CHANGE', {'BARCODE':Old}) reads "UPDATE TABLE1 SET CHANGE = 1 WHERE BARCODE = :BARCODE" The query for system.db.runNamedQuery('M5Waffles/addM5COMP') reads "INSERT INTO TABLE2 (PART, LOT) SELECT PART, LOT FROM TABLE1 WHERE CHANGE = 1" ``` lotpart = system.db.runNamedQuery('M5Waffles/getM5LOTPART', {'KIT':KIT}) #returns 2 columns of numbers 'LOT' and 'PART' prevLot = None #initializing variable prevPart = None #initializing variable for row in range(lotpart.getRowCount()): #reading values lot = lotpart.getValueAt(row, 'LOT') part = lotpart.getValueAt(row, 'PART') lot = str(lot) part = str(part) if not (prevLot == lot) and not (prevPart == part): system.db.runNamedQuery('M5Waffles/updateM5CHANGE', {'BARCODE':Old}) #changes values in another column in table system.db.runNamedQuery('M5Waffles/addM5COMP') #SQL statement is supposed to take values updated in the query above and place them in another table system.perspective.openPopup('M5serialID', 'MRSI M5/Serial affected') prevLot = lot prevPart = part ``` ---
**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:** [August 8, 2023, 1:12pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/2 "2023-08-08T13:12:05Z")
How do you know that the whole thing isn't getting called twice? There are places in Perspective where that happens. Share more about the component/view/session environment that calls this script. ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 1:13pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/3 "2023-08-08T13:13:26Z")
This script only runs when a button is clicked ---
**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:** [August 8, 2023, 1:17pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/4 "2023-08-08T13:17:10Z")
Have you put logging at the beginning of the script? If so, does it log twice? Share more. ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 1:21pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/5 "2023-08-08T13:21:04Z")
I do log some numbers in the beginning but they aren't logged twice ---
**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 8, 2023, 1:25pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/6 "2023-08-08T13:25:15Z")
My initial gut says that it probably has something to do with ` if not (prevLot == lot) and not (prevPart == part):` ~~That's a weird way to write it, I would suggest `if prevLot is not lot and prePart is not part:` since you're comparing strings.~~ Although, better might be ` if all((prevLot is not lot),(prevPart is not part):` ~~This has to do with strings being immutable and all that, but without getting to technical, Not only is it better I also think it more readable.~~ EDIT: Apparently I remembered this wrong. I need more coffee today. What happens if you put a `system.perspective.print('New Lot or Part')` after the conditional? ```auto if all([prevLot != lot,prevPart != part]): system.perspective.print('New Lot or Part') ``` Does it print out more times than you expect? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [August 8, 2023, 1:26pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/7 "2023-08-08T13:26:06Z")
One other thing I would note is that the parameters are `{'BARCODE':Old}` but `Old` is not defined in the loop, so I assume it's defined before the loop. So even if those conditions are fixed, it seems like it will always run the same query. ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [August 8, 2023, 1:28pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/8 "2023-08-08T13:28:26Z")
> [@Christy\_Jean](#): > > Everything works as intended except for the "system.db.runNamedQuery('M5Waffles/addM5COMP')" which is supposed to take values from a table and place them in another table, but it does it twice. Share your query. You aren't passing in any values in the loop so you're grabbing the values via the query. Hard to troubleshoot inserting too many values when we can't see what is actually selecting and inserting them... ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 2:03pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/9 "2023-08-08T14:03:40Z")
The "if prevLot..." statement works but still shows up twice in the table.The "if all..." statement doesn't work at all. ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 2:07pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/10 "2023-08-08T14:07:40Z")
Query says "INSERT INTO TABLE2 (PART, LOT) SELECT PART, LOT FROM TABLE1 WHERE CHANGE = 1" CHANGE is the column being updated in the query system.db.runNamedQuery('M5Waffles/updateM5CHANGE', {'BARCODE':Old}) ---
**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 8, 2023, 2:11pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/11 "2023-08-08T14:11:25Z")
oops, that's what I get for not trying it in the console. 😮‍💨 You need to wrap the conditions in a list. ```auto all([prevLot != lot,prevPart != part]) ``` Did you add the `system.perspective.print()` to the script? Did it print out more than you expected? ---
**Author:** ![djs](https://avatars.discourse-cdn.com/v4/letter/d/e47774/32.png) [@djs](https://forum.inductiveautomation.com/u/djs)\ **Post date:** [August 8, 2023, 2:25pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/12 "2023-08-08T14:25:57Z")
> [@bkarabinchak.psi](#): > > One other thing I would note is that the parameters are `{'BARCODE':Old}` but `Old` is not defined in the loop, so I assume it's defined before the loop. So even if those conditions are fixed, it seems like it will always run the same query. I agree, seems like for any lot and part _pair_ that is different from the _pair_ right before it in the dataset, you run the exact same code. Unless the `M5Waffles/updateM5CHANGE` query somehow changes with each iteration of the loop, its just going to run the exact same queries multiple times. What does the `M5Waffles/updateM5CHANGE` query look like? ---
**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 8, 2023, 2:28pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/13 "2023-08-08T14:28:54Z")
Can you provide the entirety of the script or show how or where the value for `Old` is coming from? ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 2:29pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/14 "2023-08-08T14:29:02Z")
This works but it still shows up twice in table ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 2:30pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/15 "2023-08-08T14:30:34Z")
it says "UPDATE TABLE1 SET CHANGE = 1 WHERE BARCODE = :BARCODE" ---
**Author:** ![Christy\_Jean](https://avatars.discourse-cdn.com/v4/letter/c/977dab/32.png) [@Christy\_Jean](https://forum.inductiveautomation.com/u/Christy_Jean)\ **Post date:** [August 8, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/16 "2023-08-08T14:33:36Z")
`Old ` is a barcode value that is manually inserted in a textbox ---
**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:** [August 8, 2023, 2:39pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/17 "2023-08-08T14:39:39Z")
> [@Christy\_Jean](#): > > "INSERT INTO TABLE2 (PART, LOT) SELECT PART, LOT FROM TABLE1 WHERE CHANGE = 1 Are you sure `TABLE1` only has one row with `CHANGE = 1` ? ---
**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 8, 2023, 2:47pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/18 "2023-08-08T14:47:01Z")
> [@Christy\_Jean](#): > > manually inserted in a textbox So it is constant throughout the whole loop. Since the query: ```auto UPDATE TABLE1 SET CHANGE=1 WHERE BARCODE = :BARCODE ``` Is not specific to either the part number or the lot number, there really is no reason it should be in the loop. I see you're opening a popup. Is the Idea that you run through the loop until the first lot or part is found to be different? I ask because you are initializing prevPart and prevLot to `None`, so the first time through the loop you will run your queries. Then the next time they are different you will run them again, but since Old hasn't changed the queries will do exactly the same thing. What happens if you initialize prevLot and prevPart to the values in the first row? ```auto prevLot = str(lotpart.getValueAt(0,'LOT') prevPart = str(lotpart.getValueAt(0,'PART') for row in range(1,lotpart.rowCount): ``` Also, not sure why you're converting the "numbers" to a string, if they are numbers you should be comparing them as numbers not strings. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [August 8, 2023, 3:02pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/19 "2023-08-08T15:02:18Z")
> [@pturmel](#): > > Are you sure `TABLE1` only has one row with `CHANGE = 1` ? Good question. 1. Where is CHANGE being set back to 0 (or anything other than 1)? Is `TABLE1.CHANGE` used by anything other than the update? If not, could you use one query instead? Something like ```auto INSERT INTO table2 (part, lot) SELECT part, lot FROM table1 WHERE barcode = :barcode ``` 1. How many rows are being returned by MSWaffles/getMSLOTPART'? The code as shown will insert as many rows into `table2` as there are rows returned from getMSLOTPART. The rows in `table2` will all be the same (unless the popup is changing `old`. If `table1.changed` isn't being changed back to 0 somewhere that you aren't showing that is executing inside the loop then first `INSERT` will happen _n_ times (where _n_ is the count of lot/parts in the kit?), the second `INSERT` will happen _n-1_ times, etc. 2. Is there a unique constraint on table1.barcode? Are you sure there is only one matching record for it? ---
**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 8, 2023, 3:04pm UTC](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567/20 "2023-08-08T15:04:59Z")
> [@Tim\_Carlton](#): > > unless the popup is changing `old` The pop up can't change old as old is not modified anywhere in the loop. Old is (in the scope of the loop) constant. [Next page](https://forum.inductiveautomation.com/t/sql-statement-inserting-record-twice/77567.md?page=2)