Golang Update PostgreSQL MySQL

• API
• Example
• Bulk-update
• Maps
• Omit zero values
• FQN

API

To see the full list of supported methods, see API reference.

db.NewUpdate().
	With("cte_name", subquery).

	Model(&strct).
	Model(&slice).
	Model(&map). // only map[string]interface{}

	Column("col1", "col2"). // list of columns to update
	ExcludeColumn("col1"). // all columns except col1
	ExcludeColumn("*"). // exclude all columns

	Table("table1", "table2"). // quotes table names
	TableExpr("table1 AS t1"). // arbitrary unsafe expression
	TableExpr("(?) AS alias", subquery).
	ModelTableExpr("table1 AS t1"). // overrides model table name

	Value("col1", "expr1", arg1, arg2). // overrides column value

    // Generates `SET col1 = 'value1'`
	Set("col1 = ?", "value1").
    SetColumn("col1", "?", "value1").

	OmitZero() // don't update struct fields having zero values

	WherePK(). // where using primary keys
	Where("id = ?", 123).
	Where("name LIKE ?", "my%").
	Where("? = 123", bun.Ident("id")).
	Where("id IN (?)", bun.In([]int64{1, 2, 3})).
	Where("id IN (?)", subquery).
	Where("FALSE").WhereOr("TRUE").
	WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
		return q.WhereOr("id = 1").
			WhereOr("id = 2")
	}).

	Returning("*").
	Returning("col1, col2").
	Returning("NULL"). // don't return anything

	Exec(ctx)

Example

To update a row, define a model and use UpdateQuery:

book := &Book{ID: 123, Title: "hello"}

res, err := db.NewUpdate().Model(book).WherePK().Exec(ctx)

To update a single column:

book.Title = "hello"

res, err := db.NewUpdate().
	Model(book).
	Column("title").
	Where("id = ?", 123).
	Exec(ctx)

UPDATE books SET title = 'my title' WHERE id = 123

Alternatively:

res, err := db.NewUpdate().
    Model(book).
    Set("title = ?", "hello").
    Where("id = ?", 123).
    Exec(ctx)

Bulk-update

To bulk-update books, you can use a CTE:

values := db.NewValues(&[]*Book{book1, book2})

res, err := db.NewUpdate().
	With("_data", values).
	Model((*Book)(nil)).
	TableExpr("_data").
	Set("title = _data.title").
	Set("text = _data.text").
	Where("book.id = _data.id").
	Exec(ctx)

WITH _data (id, title, text) AS (
  VALUES
    (1, 'title1', 'text1'),
    (2, 'title2', 'text2')
)
UPDATE books AS book
SET title = _data.title, text = _data.text
FROM _data
WHERE book.id = _data.id

Alternatively, you can use Bulk helper which creates a CTE for you:

res, err := db.NewUpdate().
	Model(&books).
	Column("title", "text").
	Bulk().
	Exec(ctx)

Maps

To update using a map[string]interface{}:

value := map[string]interface{}{
	"title": "title1",
	"text":	 "text1",
}
res, err := db.NewUpdate().
	Model(&value).
	TableExpr("books").
	Where("id = ?", 1).
	Exec(ctx)

UPDATE books
SET title = 'title1', text = 'text2'
WHERE id = 1

Omit zero values

You can also tell Bun to omit zero struct fields, for example, the following query does not update email column because it contains an empty value:

type User struct {
	ID	  int64
	Name  string
	Email string
}

res, err := db.NewUpdate().
	Model(&User{ID: 1, Name: "John Doe"}).
	OmitZero().
	WherePK().
	Exec(ctx)

UPDATE users
SET name = "John Doe"
WHERE id = 1

FQN

Multi-table updates differ in PostgreSQL and MySQL:

-- PostgreSQL
UPDATE dest FROM src SET col1 = src.col1 WHERE dest.id = src.id

-- MySQL
UPDATE dest, src SET dest.col1 = src.col1 WHERE dest.id = src.id

Bun helps you write queries for both databases by providing SetColumn method:

res, err := db.NewUpdate().
	Table("dest", "src").
	SetColumn("col1", "src.col1").
	Where("dest.id = src.id").
	Exec(ctx)

If you have a slice of models to update, use Bulk method:

res, err := db.NewUpdate().
	Model(&models).
	Column("col1").
	Bulk().
	Exec(ctx)