CODEWITHSUNDEEP
mysqlsqlgosql-update
Justin Johnson
Justin Johnson

Reputation: 31

Error MySQL in Go

This code is working:

stmt, err := db.Prepare("UPDATE `test` SET `score` = ? WHERE id = ?")
CheckErr(err)
_, err = stmt.Exec(value, id)

But when I change my code, it doesn't work:

stmt, err := db.Prepare("UPDATE `test` SET ? = ? WHERE id = ?")
CheckErr(err)
_, err = stmt.Exec("score", value, id)

What's the problem?

Upvotes: 0

Views: 368

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

For better or worse, parameters can only be used for literal constants inside a query. These are generally comparison values in the where clause, sometimes constants in the select or set clauses -- and less often in other parts of the query.

Identifiers are not literal constants. In fact, none of the following are:

  • database, table, and column names
  • function names
  • operators (such as +)
  • keywords (such as asc/desc in order by)

Unfortunately, to implement these "dynamically", you need to munge the query string, by directly modifying the string. That is rather yucky, but there no alternatively.

One of the benefits of this approach is that it allows the database to store and then re-use the query plan. Eliminating the compilation phase can be an important performance gain for very fast queries.

EDIT:

I do not really know go, but the idea is:

sql := "UPDATE `test` SET [col] = ? WHERE id = ?"

sql = strings.replace(sql, "[col]", "score")

stmt, err := db.Prepare(sql)
CheckErr(err)
_, err = stmt.Exec(value, id)

In other words, directly change the query string for identifiers. Continue to use parameters for values.

Upvotes: 1

JERRY
JERRY

Reputation: 1173

You must need to mention column name in database update statement. without column name in set line of update query, it will not be prepared.

Upvotes: 0

Related Questions