Update [PostgreSQL MySQL]

API

For the full list of supported methods, see API referenceopen in new window.

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

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

8. Column("col1", "col2"). // list of columns to insert
9. ExcludeColumn("col1"). // all columns except col1
10. ExcludeColumn("*"). // exclude all columns

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

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

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

23. WherePK(). // where using primary keys
24. Where("id = ?", 123).
25. Where("name LIKE ?", "my%").
26. Where("? = 123", bun.Ident("id")).
27. Where("id IN (?)", bun.In([]int64{1, 2, 3})).
28. Where("id IN (?)", subquery).
29. Where("FALSE").WhereOr("TRUE").
30. WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
31. return q.WhereOr("id = 1").
32. WhereOr("id = 2")
33. }).

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

39. Exec(ctx)

Example

To update a row, define a model and use UpdateQueryopen in new window:

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

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

To update a single column:

1. book.Title = "hello"

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

1. UPDATE books SET title = 'my title' WHERE id = 1

Alternatively:

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

Bulk-update

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

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

3. res, err := db.NewUpdate().
4. With("_data", values).
5. Model((*Book)(nil)).
6. TableExpr("_data").
7. Set("title = _data.title").
8. Set("text = _data.text").
9. Where("book.id = _data.id").
10. Exec(ctx)

1. WITH _data (id, title, text) AS (
2. VALUES
3. (1, 'title1', 'text1'),
4. (2, 'title2', 'text2')
5. )
6. UPDATE books AS book
7. SET title = _data.title, text = _data.text
8. FROM _data
9. WHERE book.id = _data.id

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

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

Maps

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

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

1. UPDATE books
2. SET title = 'title1', text = 'text2'
3. 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:

1. type User struct {
2. ID      int64
3. Name  string
4. Email string
5. }

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

1. UPDATE users
2. SET name = "John Doe"
3. WHERE id = 1

FQN

Multi-table updates differ in PostgreSQL and MySQL:

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

4. -- MySQL
5. 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:

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

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

1. res, err := db.NewUpdate().
2. Model(&models).
3. Column("col1").
4. Bulk().
5. Exec(ctx)