Update
Update existing rows using the shared DB API across supported drivers.
Use db.Update when you want to execute an update statement.
Update is a semantic wrapper around Exec.
It does not generate SQL, inspect the statement, or validate that the statement is an UPDATE.
It only makes application code easier to read.
result, err := db.Update(ctx, conn, updateUserQuery, active, id)The same db.Update helper can be used with MySQL, PostgreSQL, and Scylla.
Only the SQL or CQL syntax and placeholder style differ by driver.
Overview
| Driver | Placeholder style | Common usage |
|---|---|---|
| MySQL | ? | update rows and read RowsAffected() |
| Postgres | $1, $2 | update rows and read RowsAffected() |
| Scylla | ? | update query tables using CQL |
MySQL Update
MySQL uses ? placeholders.
const updateUserQuery = `
UPDATE users
SET active = ?
WHERE id = ?
`Use db.Update:
func UpdateUserActive(ctx context.Context, conn db.Conn, id int64, active bool) (db.Result, error) {
return db.Update(ctx, conn, updateUserQuery, active, id)
}Usage:
result, err := UpdateUserActive(ctx, conn, 1, false)
if err != nil {
return err
}
fmt.Printf("rows_affected=%d\n", result.RowsAffected())PostgreSQL Update
PostgreSQL uses numbered placeholders.
const updateUserQuery = `
UPDATE users
SET active = $1
WHERE id = $2
`Use db.Update the same way:
func UpdateUserActive(ctx context.Context, conn db.Conn, id int64, active bool) (db.Result, error) {
return db.Update(ctx, conn, updateUserQuery, active, id)
}Usage:
result, err := UpdateUserActive(ctx, conn, 1, false)
if err != nil {
return err
}
fmt.Printf("rows_affected=%d\n", result.RowsAffected())Scylla Update
Scylla uses ? placeholders.
const updateUserByIDQuery = `
UPDATE users_by_id
SET active = ?
WHERE id = ?
`Use db.Update:
func UpdateUserActive(ctx context.Context, conn db.Conn, id string, active bool) (db.Result, error) {
return db.Update(ctx, conn, updateUserByIDQuery, active, id)
}For query-driven Scylla models, the same logical update may need to be applied to more than one query table.
const updateUserByEmailQuery = `
UPDATE users_by_email
SET active = ?
WHERE email = ?
`
func UpdateUserActiveByIDAndEmail(
ctx context.Context,
conn db.Conn,
id string,
email string,
active bool,
) error {
if _, err := db.Update(ctx, conn, updateUserByIDQuery, active, id); err != nil {
return err
}
if _, err := db.Update(ctx, conn, updateUserByEmailQuery, active, email); err != nil {
return err
}
return nil
}RowsAffected
db.Update returns db.Result.
type Result interface {
RowsAffected() int64
LastInsertId() int64
}Use RowsAffected to inspect how many rows were changed.
result, err := db.Update(ctx, conn, updateUserQuery, active, id)
if err != nil {
return err
}
fmt.Println(result.RowsAffected())LastInsertId is usually not relevant for update statements.
Update vs Exec
Update is equivalent to Exec.
result, err := db.Update(ctx, conn, query, args...)is equivalent to:
result, err := db.Exec(ctx, conn, query, args...)Use Update when you want the code to communicate update intent.
Use Exec when the statement is generic.
Dialect Update
For applications that support multiple drivers, keep update statements in db.DialectSQL.
type Queries struct {
UpdateUserActive db.DialectSQL `json:"UpdateUserActive"`
}Then select the correct SQL or CQL for the active driver:
q, err := db.Dialect(conn, queries.UpdateUserActive, active, id)
if err != nil {
return err
}
result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
return err
}This keeps the repository code independent from the selected driver while keeping SQL explicit.
When to Use Update
Use db.Update when:
- the operation modifies existing rows
- you want readable repository code
- the statement does not return rows
- you want access to
RowsAffected - the operation should communicate update intent
When Not to Use Update
Do not use db.Update for statements that return rows.
For PostgreSQL statements using RETURNING, use db.Get, db.Value, or query-based helpers instead.
Example:
updatedAt, found, err := db.Value[time.Time](ctx, conn, `
UPDATE users
SET active = $1
WHERE id = $2
RETURNING updated_at
`, active, id)Use db.Exec when the statement is not clearly an insert, update, or delete.
Recommended Style
Define the query as a constant:
const updateUserQuery = `
UPDATE users
SET active = ?
WHERE id = ?
`Wrap it in a small function:
func UpdateUserActive(ctx context.Context, conn db.Conn, id int64, active bool) (db.Result, error) {
return db.Update(ctx, conn, updateUserQuery, active, id)
}Use the returned result when you need to inspect affected rows:
result, err := UpdateUserActive(ctx, conn, id, false)
if err != nil {
return err
}
if result.RowsAffected() == 0 {
return errors.New("user was not updated")
}