# Updating a table **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [July 18, 2022, 1:45pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004 "2022-07-18T13:45:41Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [July 18, 2022, 1:45pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/1 "2022-07-18T13:45:41Z")
I have 2 Named queries that generate tables, here they are: ```auto SELECT VP.PERSONNUM AS 'BadgeNum' , FORMAT( VP.INPUNCHDTM, 'hh:mm') AS 'ClockINTime' , VP.PERSONFULLNAME AS 'Name' , HOMELABORLEVELNM5 AS 'Shift' , HOMELABORLEVELNM6 AS 'Dept' , HOMELABORLEVELDSC7 AS 'JobTitle' , HOMELABORLEVELDSC4 AS 'Supervisor' FROM VP_EMPCUREARNTIME AS VP LEFT OUTER JOIN VP_EMPLOYEEV42 ON VP.PERSONNUM = VP_EMPLOYEEV42.PERSONNUM LEFT OUTER JOIN TIMEZONE ON VP_EMPLOYEEV42.TIMEZONENAME = TIMEZONE.ENGLISH WHERE (VP.LABORLEVELNAME3 = '0065') AND VP.INPUNCHDTM is not Null AND VP.LABORLEVELNAME5 NOT IN ('9', 'S') AND (VP.STARTDTM > DATEADD(d, - 1, GETDATE())) AND (VP.GMTSTARTDTM <= DATEADD(S, - TIMEZONE.GMTOFFSET, GETDATE())) AND (VP.GMTENDDTM IS NULL OR VP.GMTENDDTM >= DATEADD(S, - TIMEZONE.GMTOFFSET, GETDATE())) order by HOMELABORLEVELNM6, HOMELABORLEVELDSC7 ``` AND ```auto Select * From SM_CheckIn ``` The plan is to have the data from the first table be moved into the second by selecting a row and pressing a button with this script on it: ```auto Time = system.date.now() Name = self.session.custom.Name ID = self.session.custom.Badge_Num system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID]) ``` It is not doing that, so I think something is wrong with this script. If so, can someone help point it out? Thank you ---
**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:** [July 18, 2022, 1:55pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/2 "2022-07-18T13:55:27Z")
I’m assuming Perspective, based on your script. Since the script runs on the gateway, you’ll need to specify the db connection in runPrepUpdate() ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [July 18, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/3 "2022-07-18T14:26:34Z")
As in: dbo.SM\_CheckIN ? ---
**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:** [July 18, 2022, 2:31pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/4 "2022-07-18T14:31:08Z")
The name of the DB connection in Ignition. Gateway\>Databases\>Connections ```auto system.db.runPrepUpdate(query,params,dbConnection) ``` ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 18, 2022, 2:44pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/5 "2022-07-18T14:44:06Z")
> [@mjohnson0238](#): > > ```auto > Time = system.date.now() > Name = self.session.custom.Name > ID = self.session.custom.Badge_Num > system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID]) > > ``` Are you inserting rows or modifying rows. ```auto system.db.runPrepUpdate("INSERT INTO order_requests (Reason, InternalDescription) VALUES (?,?)", [Reason, InternalDescription]) ``` ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [July 18, 2022, 2:44pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/6 "2022-07-18T14:44:26Z")
Oh ok, since my dbconnection is IgnitionHud it would be: ```auto system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID], IgnitionHud) ``` ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [July 18, 2022, 2:46pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/7 "2022-07-18T14:46:17Z")
the SM\_CheckIn is an empty table at the start then when a row from the 1st table is selected and the button pressed it moves a row into the SM\_CheckIn table ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 18, 2022, 2:50pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/8 "2022-07-18T14:50:32Z")
Then to me it should be an insert query. ```auto system.db.runPrepUpdate("INSERT INTO SM_CheckIn (Time, Name, ID) VALUES (?,?,?)", [Time, Name, ID]) ``` ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 18, 2022, 2:53pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/9 "2022-07-18T14:53:26Z")
Also, none of my perspective update queries have the DB connection in. I assume if none is specified it uses the default ? ---
**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:** [July 18, 2022, 3:07pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/10 "2022-07-18T15:07:07Z")
Perspective scripts can use the projects default database connection, assuming that one has been provided. See this thread for confirmation of that. > [@Perspective Workstation architecture question](https://forum.inductiveautomation.com/t/perspective-workstation-architecture-question/59480/25): > > Perspective is running inside the context of a particular project (by definition), so on the backend, we pass the project’s default database in to the running script’s context automatically. The individual scripting function implementations “know” to look for this context and extract it, so both things are true: All Perspective scripts run on the gateway Perspective scripts can assume the default database. See [this thread](https://forum.inductiveautomation.com/t/bug-5802-async-tag-event-script-loses-reference-paths/57768) if you’re interested in how that mechanism actually works. However, I don't like to assume that the system will know the correct connection, (because multiple connections, multiple people working on the same project, etc...). So I always provide the db connection because then it will always work as expected (assuming the query and params are correct) ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [July 18, 2022, 6:07pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/11 "2022-07-18T18:07:11Z")
So I have tried adding the db connection and INSERT TO command. I still dont get the row moved into the other table. In the log i get this error: com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 6, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at jdk.internal.reflect.GeneratedMethodAccessor44.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE IgnitionHud.dbo.SM\_CheckIn SET WHERE Time = ?, Name = ?, ID = ?, [Mon Jul 18 13:06:03 CDT 2022, Macht III, Howard A, 0019378826], IgnitionHud, , false, false) ---
**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:** [July 18, 2022, 6:16pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/12 "2022-07-18T18:16:21Z")
There should be a 'Caused by' line somewhere in the error. > [@craigb](#): > > Also, none of my perspective update queries have the DB connection in. I assume if none is specified it uses the default ? I've gotten into the habit of always specifying. That way there is no confusion over the scope. 😉 ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 18, 2022, 6:25pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/13 "2022-07-18T18:25:52Z")
> [@mjohnson0238](#): > > `system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID], IgnitionHud)` The way I read that statement and I could be wrong, wouldn't be the first or last time. Your asking to update the database with a value "SET". Your also asking it to only set that value WHERE the following is true. Your table is empty initially so nothing is going to match that statement. ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 18, 2022, 6:29pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/14 "2022-07-18T18:29:05Z")
> [@JordanCClark](#): > > I’ve gotten into the habit of always specifying. That way there is no confusion over the scope. 😉 I'm learning that there is one way to do things. I'll rephrase that, there is many ways to do things but only one way to do it properly. I'll start doing things the proper way 🙂 ---
**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\ **Post date:** [July 18, 2022, 6:37pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/15 "2022-07-18T18:37:52Z")
here is the caused by line: ```auto Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 6, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at jdk.internal.reflect.GeneratedMethodAccessor44.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE IgnitionHud.dbo.SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?, [Mon Jul 18 13:36:42 CDT 2022, Holland, Chester C, 0002546828], IgnitionHud, , false, false) ... 36 common frames omitted Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE IgnitionHud.dbo.SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?, [Mon Jul 18 13:36:42 CDT 2022, Holland, Chester C, 0002546828], IgnitionHud, , false, false) ... 35 common frames omitted ``` ---
**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:** [July 18, 2022, 6:40pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/16 "2022-07-18T18:40:30Z")
That SQL is not valid. There should be your value assignments between **SET** and **WHERE**. The conditions after `WHERE` need to uniquely identify the existing row in the DB that you wish to alter. You should look at the documentation for your database for the proper syntax for an `UPDATE` statement. ---
**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\ **Post date:** [July 18, 2022, 6:50pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/17 "2022-07-18T18:50:03Z")
> [@craigb](#): > > `system.db.runPrepUpdate("INSERT INTO SM_CheckIn (Time, Name, ID) VALUES (?,?,?)", [Time, Name, ID])` That should have worked, in theory.. I see in the error log it looks like its passing 4 variables as the name is comma separated, would that throw it off ? ```auto [Mon Jul 18 13:36:42 CDT 2022, Holland, Chester C, 0002546828] ``` ---
**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:** [July 18, 2022, 7:00pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/18 "2022-07-18T19:00:30Z")
> [@craigb](#): > > as the name is comma separated, would that throw it off ? No, those are properly protected as query parameters. ---
**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:** [July 18, 2022, 7:41pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/19 "2022-07-18T19:41:15Z")
As @pturmel pointed out, the SQL syntax you used is invalid. That being said, since it appears that you may need to `INSERT or UPDATE IF EXISTS` it seems that you may need a bit more complex of a query. Like the `MERGE INTO` statement for MSSQL or `ON DUPLICATE KEY UPDATE` for MySQL/MariaDB (sorry don’t know the equivalent for postgres). ---
**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:** [July 18, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/updating-a-table/62004/20 "2022-07-18T19:57:13Z")
> [@lrose](#): > > don’t know the equivalent for postgres "ON CONFLICT" [Next page](https://forum.inductiveautomation.com/t/updating-a-table/62004.md?page=2)