# Insert or update based on selected row **URL:** \ **Category:** Ignition\ **Tags:** vision\ **Created:** [March 5, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244 "2021-03-05T19:38:31Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 5, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/1 "2021-03-05T19:38:31Z")
hi, I know this is very old but I tried the code you suggested above to see if it works for me. I’m trying to insert or update a row in the table based on if or not I find a similar value in an existing row. Could you take a look and see what I’m missing or doing worng? Thanks! ![script](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/4/74bb60c75918995c792cf8ce9090911ccfdf9c11.jpeg) ---
**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:** [March 5, 2021, 8:24pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/2 "2021-03-05T20:24:53Z")
Either you haven’t provided the entire script, or `r` is undefined. Though I’m pretty certain that if this is the entire script you will get an error. I believe that `r` should be `row` Post the entire script or at least all pertinent parts and use the preformatted text option (\ button) to post your code, as opposed to a screen shot. Also you need to provide a column number or the column name as a string literal. ```auto for row in range(data.rowCount): if data.getValueAt(row,'serialnumber'): #run update query else: #run insert query ``` ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 5, 2021, 8:41pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/3 "2021-03-05T20:41:09Z")
This is my entire script I replaced r with row but still won’t work ```auto if previousValue != currentValue and initialChange == False: laserdata = system.tag.read("[.]Laser_Sink_Number").value db = 'PostgreSQL' if laserdata == 'BARCODE READ FAIL': laser = 'Scan Failed' system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)", [laser], db) else: laser = 'Scan Successful' table = event.source.parent.getComponent('packtrack') data = table.data for row in range(data.rowCount): if data.getValueAt(row, serialnumber) == laserdata: system.db.runPrepUpdate("UPDATE packtrack SET lasersink=?, lasertime=CURRENT_TIMESTAMP WHERE serialnumber=?", [laser, laserdata], db) else: system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)", [laser], db) ``` ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 5, 2021, 8:44pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/4 "2021-03-05T20:44:00Z")
as a note, the script works at: if laserdata == ‘BARCODE READ FAIL’: it deosn’t work from here down: else: laser = ‘Scan Successful’ ---
**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:** [March 5, 2021, 8:46pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/5 "2021-03-05T20:46:08Z")
I changed two things: The column name must be supplied as a string, or you need to provide the column index. ```auto if data.getValueAt(row, 'serialnumber') == laserdata: ``` If you are receiving an error, what is it? ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 5, 2021, 9:27pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/6 "2021-03-05T21:27:42Z")
I don’t get an error I changed the column as a string but still no good ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 8, 2021, 9:10pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/7 "2021-03-08T21:10:08Z")
I tried a different approach but it still won’t work and I don’t get any error: ```auto if currentValue.value == True: laserdata = system.tag.read("[.]Laser_Sink_Number").value db = 'PostgreSQL' if system.tag.read("[.]Laser_Barcode_Error").value == True: laser = 'Scan Failed' system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)", [laser], db) else: laser = 'Scan Successful' system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, serialnumber, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)", [laserdata, laser], "ON CONFLICT (serialnumber) DO UPDATE SET lasersink=?, lasertime=CURRENT_TIMESTAMP WHERE serialnumber=?", [laser, laserdata], db) ``` ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [March 8, 2021, 9:44pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/8 "2021-03-08T21:44:35Z")
```auto system.db.runPrepUpdate(query, args, query2, args, db) ``` This isn’t valid syntax - you can only provide one query string and one set of arguments to `runPrepUpdate`. Barring anything else (once again: have you added print statements to verify the rest of your logic?) you should be sending a single SQL query. I’d highly recommend using multi-line strings to make your query more readable, as well: ```auto if currentValue.value: laserdata = system.tag.read("[.]Laser_Sink_Number").value db = 'PostgreSQL' if system.tag.read("[.]Laser_Barcode_Error").value: laser = 'Scan Failed' query = """ INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?) """ system.db.runPrepUpdate(query, [laser], db) else: laser = 'Scan Successful' query = """ INSERT INTO packtrack (generictime, lasertime, serialnumber, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?) ON CONFLICT (serialnumber) DO UPDATE SET lasersink=?, lasertime=CURRENT_TIMESTAMP WHERE serialnumber=? """ system.db.runPrepUpdate(query, [laserdata, laser, laser, laserdata], db) ``` ---
**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\ **Post date:** [March 8, 2021, 11:20pm UTC](https://forum.inductiveautomation.com/t/insert-or-update-based-on-selected-row/44244/9 "2021-03-08T23:20:06Z")
please excuse my ignorance 🙁, where would I see the print result?