How to update multiple rows using sqlx?

Question

I'd like to update multiple rows in a single query:

//threadIDs is a variable length slice of integers like [3 5 6]
query := "UPDATE message SET recipient_deleted=? WHERE recipient_id=? AND thread_id IN ?"
_, err := database.SQL.Exec( query, 1, userID, threadIDs)
if err != nil {
    log.Fatal(err)
}

But I get this runtime error:

sql: converting argument $3 type: unsupported type []int, a slice of int

How can I fix this?

given this answer https://stackoverflow.com/questions/53983170/sql-converting-argument-1-type-unsupported-type-int-a-slice-of-in I think if the number of elements in threadIDs is fixed then using multiple ?s and threadID[1],threadIDs[2] etc will also work. — R.S., Jun 12 '19 at 19:15
@R.S. the number of `threadIDs` is not fixes. It's user submitted data. — grhn, Jun 12 '19 at 19:16
You'll need to turn the slice of ints into a string representing an array and then cast the parameter reference in the query, like here: https://stackoverflow.com/questions/38036752/go-and-in-clause-in-postgres. Or alternatively use `sqlx`'s `In` helper, like here: https://stackoverflow.com/questions/40565805/how-to-use-sqlx-to-query-mysql-in-a-slice — mkopriva, Jun 12 '19 at 19:16
@mkopriva, I could not figure out how to use this answer https://stackoverflow.com/questions/40565805/how-to-use-sqlx-to-query-mysql-in-a-slice in my particular use case. I appreciate if you elaborate the answer with code. — grhn, Jun 12 '19 at 19:19
... also if you don't want to manually convert the slice you can use something like `pq.Array` or `pq.Int64Array` or if `sqlx` has some equivalent to that use that. Have you tried this https://play.golang.com/p/Qwqb_v2oSZ0? What error do you get if you do? — mkopriva, Jun 12 '19 at 19:27
@mkopriva the playground code gives `database.SQL.In undefined (type *sqlx.DB has no field or method In)` — grhn, Jun 12 '19 at 19:30
It's just example code you should try on your own machine, playground doesn't have a database to connect to, neither does it support 3rd party package imports, at least not yet. — mkopriva, Jun 12 '19 at 19:31
@grhn my bad I misunderstood your comment, and also it should be `sqlx.In` not `database.SQL.In` in that code. — mkopriva, Jun 12 '19 at 19:43
Great! This modified one works. `query := "UPDATE message SET recipient_deleted=? WHERE recipient_id=? AND thread_id IN (?)" qry, args, err := sqlx.In(query, 1, userID, threadIDs) if err != nil { panic(err) } if _, err := database.SQL.Exec(qry, args...); err != nil { panic(err) }` please answer and I'll accept it. Thank you so much! — grhn, Jun 12 '19 at 19:46

score 4 · Accepted Answer · answered Jun 12 '19 at 20:28

4

You can use sqlx.In which returns a new query and a new args slice that you then use instead of your original values, like so:

query := "UPDATE message SET recipient_deleted=? WHERE recipient_id=? AND thread_id IN (?)"

qry, args, err := sqlx.In(query, 1, userID, threadIDs)
if err != nil {
    panic(err)
}

if _, err := database.SQL.Exec(qry, args...); err != nil {
    panic(err)
}

answered Jun 12 '19 at 20:28

mkopriva

35,176
4
57
71

You should rebind your `qry` before `Exec`. Just like: `database.SQL.Exec(database.SQL.Rebind(qry), args...)` – Reed Chan Dec 25 '19 at 11:58

How to update multiple rows using sqlx?

1 Answers1