# Update with IN condition

**URL:** <https://forum.inductiveautomation.com/t/update-with-in-condition/11679>\
**Category:** Ignition\
**Created:** [May 17, 2016, 8:20am UTC](https://forum.inductiveautomation.com/t/update-with-in-condition/11679 "2016-05-17T08:20:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![andrea.montresor](https://avatars.discourse-cdn.com/v4/letter/a/5f9b8f/32.png) [@andrea.montresor](https://forum.inductiveautomation.com/u/andrea.montresor)\
**Post date:** [May 17, 2016, 8:20am UTC](https://forum.inductiveautomation.com/t/update-with-in-condition/11679/1 "2016-05-17T08:20:55Z")

</div>

Hi,

i want to run this update script with in where clause an IN condition.  
I want to make only one call at database. I don’t want to cycle on each value of the array.  
This is my script:

```auto
system.db.runPrepUpdate("UPDATE TEST SET FL= 1 WHERE ID IN (?)", [y], "dscada_main")
```

The y variable is an array of int value such as:  
[2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]

But I have this error:  
caused by GatewayException: SQL error for “UPDATE TEST SET FL= 1 WHERE ID IN (?)”: The conversion from UNKNOWN to UNKNOWN is unsupported.  
caused by SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

Can I solve it?

Thanks  
Andrea

---

<div class="post-metadata">

**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:** [May 17, 2016, 12:15pm UTC](https://forum.inductiveautomation.com/t/update-with-in-condition/11679/2 "2016-05-17T12:15:42Z")

</div>

Hi Andrea,

By array, do you mean a Python list? This will be my assumption unless you tell me differently.

I’d say the problem is that SQL doesn’t understand lists. Try converting the list into a string:

```auto
system.db.runPrepUpdate("UPDATE TEST SET FL= 1 WHERE ID IN (?)", [str(y)[1:-1]], "dscada_main")
```

Not that converting a list to string keeps the brackets, so the [1:-1] will remove them.  
[attachment=0]2016-05-17\_8-14-43.png[/attachment]

---

<div class="post-metadata">

**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:** [May 17, 2016, 12:58pm UTC](https://forum.inductiveautomation.com/t/update-with-in-condition/11679/3 "2016-05-17T12:58:22Z")

</div>

You cannot pass a list into a single ‘?’ substitution parameter. Jordan’s suggestion would be suitable in your case if you use ‘%s’ instead of ‘?’ and use the non-Prep form of the query.  
When using data types in a list that would be problematic when converted to a string, you must place one ‘?’ for each element of list. Like so:`system.db.runPrepUpdate("UPDATE TEST SET FL= 1 WHERE ID IN (%s)" % (", ".join(['?'] * len(y))), y, "dscada_main")`