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())MySQL Dialect Update
Use db.UpdateDialect when the MySQL update statement is stored in db.DialectSQL.
The query uses ? placeholders.
--UpdateUser
UPDATE users
SET name = ?, email = ?, active = ?
WHERE id = ?func UpdateUser(ctx context.Context, conn db.Conn, queries Queries, id int64, name string, email string, active bool) (db.Result, error) {
res, err := db.UpdateDialect(ctx, conn, queries.UpdateUser, name, email, active, id)
if err != nil {
return nil, err
}
return res, nil
}Load the query model before calling the helper.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}
res, err := UpdateUser(ctx, conn, queries, 1, "Jane Doe", "jane@example.com", false)
if err != nil {
log.Fatal(err)
}
fmt.Printf("rows_affected=%d\n", res.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())Postgres Dialect Update
Use db.UpdateDialect when the PostgreSQL update statement is stored in db.DialectSQL.
The query uses numbered placeholders.
--UpdateUser
UPDATE users
SET name = $1, email = $2, active = $3
WHERE id = $4func UpdateUser(ctx context.Context, conn db.Conn, queries Queries, id int64, name string, email string, active bool) (db.Result, error) {
res, err := db.UpdateDialect(ctx, conn, queries.UpdateUser, name, email, active, id)
if err != nil {
return nil, err
}
return res, nil
}Load the query model before calling the helper.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}
res, err := UpdateUser(ctx, conn, queries, 1, "Jane Doe", "jane@example.com", false)
if err != nil {
log.Fatal(err)
}
fmt.Printf("rows_affected=%d\n", res.RowsAffected())The Go call is the same as MySQL.
Only the SQL placeholder style changes.
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
}Scylla Dialect Update
Use db.UpdateDialect when the Scylla update statement is stored in db.DialectSQL.
Scylla uses ? placeholders.
--UpdateUser
UPDATE users_by_id
SET email = ?, active = ?
WHERE id = ?func UpdateUser(ctx context.Context, conn db.Conn, queries Queries, id string, email string, active bool) error {
_, err := db.UpdateDialect(ctx, conn, queries.UpdateUser, email, active, id)
if err != nil {
return err
}
return nil
}Load the query model before calling the helper.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}
err = UpdateUser(ctx, conn, queries, "22222222-2222-2222-2222-222222222222", "jane@example.com", false)
if err != nil {
log.Fatal(err)
}Scylla does not return a useful affected-row count.
For Scylla, treat a successful call as a successful statement execution and do not check RowsAffected().
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
Use dialect update helpers when update statements are stored in db.DialectSQL.
This does not replace db.Update.
Use db.Update when you pass a direct SQL or CQL query string.
Use db.UpdateDialect when you pass a db.DialectSQL value loaded from query models.
type Queries struct {
UpdateUser db.DialectSQL `json:"UpdateUser"`
}Load the query model before calling dialect helpers.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}See Loading Queries From SQL Files for the full LoadQueries setup.
| Driver | Direct query helper | Dialect helper | Result handling |
|---|---|---|---|
| MySQL | db.Update | db.UpdateDialect | check db.Result |
| PostgreSQL | db.Update | db.UpdateDialect | check db.Result |
| Scylla | db.Update | db.UpdateDialect | ignore result |
MySQL and PostgreSQL return a db.Result.
Scylla does not return a useful affected-row count, so Scylla examples ignore the result.
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")
}