# system.db.runPrepUpdate syntax help **URL:** \ **Category:** Ignition\ **Created:** [October 28, 2015, 10:19pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691 "2015-10-28T22:19:15Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Andre](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@Andre](https://forum.inductiveautomation.com/u/Andre)\ **Post date:** [October 28, 2015, 10:19pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/1 "2015-10-28T22:19:15Z")
Hi, I’m trying to execute this line of code in a gateway tag change script: system.db.runPrepUpdate(“INSERT INTO recipe\_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = ‘%i’ AND IngredientNumber = ‘%i’)”[True]%(RecipeID,IngredientNumSelected)) The IngredientAdded column in the recipe\_interlock table is boolean. i get the following error on this line of code when the script fires: TypeError: not all arguments converted during string formatting Whats the correct syntax for this? ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:") Andre ---
**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:** [October 29, 2015, 10:35am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/2 "2015-10-29T10:35:19Z")
Hi Andre! What’s happening is that you are mixing two different syntaxes. With runPrepUpdate, you need to use a question mark for every placeholder, and the parameters all go in one list: ```auto system.db.runPrepUpdate("INSERT INTO recipe_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = '?' AND IngredientNumber = '?')", [True, RecipeID, IngredientNumSelected]) ``` ---
**Author:** ![Andre](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@Andre](https://forum.inductiveautomation.com/u/Andre)\ **Post date:** [October 30, 2015, 3:24am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/3 "2015-10-30T03:24:12Z")
Hi Jordan, Thanks for the help, when i use your syntax i get this error Caused by: java.sql.SQLException: Parameter index out of range (2 \> number of parameters, which is 1) ---
**Author:** ![Andre](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@Andre](https://forum.inductiveautomation.com/u/Andre)\ **Post date:** [October 30, 2015, 3:27am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/4 "2015-10-30T03:27:43Z")
Actually, i added an extra variable but still should work system.db.runPrepUpdate(“INSERT INTO recipe\_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = ‘?’ AND ItemID = ‘?’ AND IngredientNumber = ‘?’)”,[True,RecipeID,ItemID,IngredientNumSelected]) ---
**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:** [October 30, 2015, 11:50am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/5 "2015-10-30T11:50:21Z")
> [@Andre](#): > > system.db.runPrepUpdate("INSERT INTO recipe\_interlock (IngredientAdded) VALUES (?) WHERE (RecipeNumber = '?' AND ItemID = '?' AND IngredientNumber = '?')",[True,RecipeID,ItemID,IngredientNumSelected]) Don't quote the question marks. ---
**Author:** ![Andre](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@Andre](https://forum.inductiveautomation.com/u/Andre)\ **Post date:** [November 1, 2015, 11:16pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/6 "2015-11-01T23:16:34Z")
I’m not 100% sure how but i got it working with this query. UPDATE and SET must be different? ```auto system.db.runPrepUpdate("UPDATE recipe_interlock SET IngredientAdded=? WHERE RecipeNumber=? AND ItemID=? AND IngredientNumber=?",[True,RecipeID,ItemID,IngredientNumSelected]) ``` Thanks for your help guys ---
**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:** [November 2, 2015, 12:44am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/7 "2015-11-02T00:44:03Z")
[quote=“Andre”]I’m not 100% sure how but i got it working with this query. UPDATE and SET must be different?[/quote]/ me smacks forehead / I just looked closer at the INSERT statement that I commented on – I was so focused on the quotes that didn’t belong with the question marks. INSERT statements that supply values don’t have WHERE clauses. You list the column names and the values that go with them:`INSERT INTO tablename (columnname1, columnname2, ...) VALUES (value1, value2, ...)`UPDATE statements list the assignments to make and a WHERE clause that limits the changes to the desired rows (or not – you can change all the rows in the table if you like):`UPDATE tablename SET columnname1=value1, columnname2=value2, ... WHERE ....` Hope that helps. ---
**Author:** ![Andre](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@Andre](https://forum.inductiveautomation.com/u/Andre)\ **Post date:** [November 2, 2015, 12:47am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/8 "2015-11-02T00:47:00Z")
That explanation should be added to the help file! ---
**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:** [November 2, 2015, 2:02am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/9 "2015-11-02T02:02:41Z")
[quote=“Andre”]That explanation should be added to the help file![/quote]Well, Ignition is agnostic on which database product you should use, and there are syntax differences, so a referral is a bit more reasonable. I’m a fan of PostgreSQL, so here you go: [Inserting Data](http://www.postgresql.org/docs/9.4/interactive/dml-insert.html) and [Updating Data](http://www.postgresql.org/docs/9.4/interactive/dml-update.html). ---
**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:** [November 2, 2015, 2:09am UTC](https://forum.inductiveautomation.com/t/system-db-runprepupdate-syntax-help/10691/10 "2015-11-02T02:09:03Z")
Here’s the corresponding Oracle online links: [Inserting Data](https://docs.oracle.com/database/121/SQLRF/statements_9014.htm#i2163698) and [Updating Data](https://docs.oracle.com/database/121/SQLRF/statements_10008.htm#i2067715).