runPrepUpdate not updating?

[danielps1818]

I have this query that works:

UPDATE DTIM_TIME_TABLE
SET LEVEL6ID = 'TEST'
WHERE LEVEL5ID IN ('TEST', 'TEST1')

but this doesn't:

list2 = [
	"TEST",
	"TEST1"
]

r = '(' + ', '.join(["'%s'" % (l) for l in list2]) + ')'
# r = ('TEST', 'TEST1')

system.db.runPrepUpdate(
	"UPDATE DTIM_TIME_TABLE SET LEVEL6ID = 'TEST' WHERE LEVEL5ID IN ?",
	[r]
)

I'm dumb? Any idea?
Thanks in advance

[Transistor]

You're not dumb. It was discussed a few times on the forum. Here's one from eight years ago.

Update with IN condition

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")

[danielps1818]

Just as documentation. I ended using system.db.runQuery() and I simply build the string.

statement = 'UPDATE DTIM_TIME_TABLE SET LEVEL6ID = 'TEST' WHERE LEVEL5ID IN "
r = '(' + ', '.join(["'%s'" % (l) for l in list2]) + ')'

query = statement + r
# \_> UPDATE DTIM_TIME_TABLE SET LEVEL6ID = 'TEST' WHERE LEVEL5ID IN ('TEST', 'TEST1')

system.db.runQuery(query)

[pturmel]

@danielps1818

I simply build the string.

That is extremely unsafe for quoted strings as you show. Don't do that. Anyone encountering this topic in the future is highly encouraged to not follow Daniel's example.