CRUD Interface - Update - 《GORM v1.23.5 Documentation》

Save All Fields
Update single column
Updates multiple columns
Update Selected Fields
Update Hooks
Batch Updates
- Block Global Updates
- Updated Records Count
Advanced

Save All Fields

Save will save all fields when performing the Updating SQL

1. db.First(&user)

3. user.Name = "jinzhu 2"
4. user.Age = 100
5. db.Save(&user)
6. // UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;

Update single column

When updating a single column with Update, it needs to have any conditions or it will raise error ErrMissingWhereClause, checkout Block Global Updates for details When using the Model method and its value has a primary value, the primary key will be used to build the condition, for example:

1. // Update with conditions
2. db.Model(&User{}).Where("active = ?", true).Update("name", "hello")
3. // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;

5. // User's ID is `111`:
6. db.Model(&user).Update("name", "hello")
7. // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

9. // Update with conditions and model value
10. db.Model(&user).Where("active = ?", true).Update("name", "hello")
11. // UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;

Updates multiple columns

Updates supports update with struct or map[string]interface{}, when updating with struct it will only update non-zero fields by default

1. // Update attributes with `struct`, will only update non-zero fields
2. db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})
3. // UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

5. // Update attributes with `map`
6. db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
7. // UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

NOTE When update with struct, GORM will only update non-zero fields, you might want to use map to update attributes or use Select to specify fields to update

Update Selected Fields

If you want to update selected fields or ignore some fields when updating, you can use Select, Omit

1. // Select with Map
2. // User's ID is `111`:
3. db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
4. // UPDATE users SET name='hello' WHERE id=111;

6. db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
7. // UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

9. // Select with Struct (select zero value fields)
10. db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})
11. // UPDATE users SET name='new_name', age=0 WHERE id=111;

13. // Select all fields (select all fields include zero value fields)
14. db.Model(&user).Select("*").Update(User{Name: "jinzhu", Role: "admin", Age: 0})

16. // Select all fields but omit Role (select all fields include zero value fields)
17. db.Model(&user).Select("*").Omit("Role").Update(User{Name: "jinzhu", Role: "admin", Age: 0})

Update Hooks

GORM allows hooks BeforeSave, BeforeUpdate, AfterSave, AfterUpdate, those methods will be called when updating a record, refer Hooks for details

1. func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
2. if u.Role == "admin" {
3. return errors.New("admin user not allowed to update")
4. }
5. return
6. }

Batch Updates

If we haven’t specified a record having primary key value with Model, GORM will perform a batch updates

1. // Update with struct
2. db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})
3. // UPDATE users SET name='hello', age=18 WHERE role = 'admin';

5. // Update with map
6. db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18})
7. // UPDATE users SET name='hello', age=18 WHERE id IN (10, 11);

Block Global Updates

If you perform a batch update without any conditions, GORM WON’T run it and will return ErrMissingWhereClause error by default

You have to use some conditions or use raw SQL or enable the AllowGlobalUpdate mode, for example:

1. db.Model(&User{}).Update("name", "jinzhu").Error // gorm.ErrMissingWhereClause

3. db.Model(&User{}).Where("1 = 1").Update("name", "jinzhu")
4. // UPDATE users SET `name` = "jinzhu" WHERE 1=1

6. db.Exec("UPDATE users SET name = ?", "jinzhu")
7. // UPDATE users SET name = "jinzhu"

9. db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User{}).Update("name", "jinzhu")
10. // UPDATE users SET `name` = "jinzhu"

Updated Records Count

Get the number of rows affected by a update

1. // Get updated records count with `RowsAffected`
2. result := db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})
3. // UPDATE users SET name='hello', age=18 WHERE role = 'admin';

5. result.RowsAffected // returns updated records count
6. result.Error        // returns updating error

Advanced

Update with SQL Expression

GORM allows updates column with SQL expression, e.g:

1. // product's ID is `3`
2. db.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))
3. // UPDATE "products" SET "price" = price * 2 + 100, "updated_at" = '2013-11-17 21:34:10' WHERE "id" = 3;

5. db.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)})
6. // UPDATE "products" SET "price" = price * 2 + 100, "updated_at" = '2013-11-17 21:34:10' WHERE "id" = 3;

8. db.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
9. // UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3;

11. db.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
12. // UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3 AND quantity > 1;

And GORM also allows update with SQL Expression/Context Valuer with Customized Data Types, e.g:

1. // Create from customized data type
2. type Location struct {
3. X, Y int
4. }

6. func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
7. return clause.Expr{
8. SQL:  "ST_PointFromText(?)",
9. Vars: []interface{}{fmt.Sprintf("POINT(%d %d)", loc.X, loc.Y)},
10. }
11. }

13. db.Model(&User{ID: 1}).Updates(User{
14. Name:  "jinzhu",
15. Location: Location{X: 100, Y: 100},
16. })
17. // UPDATE `user_with_points` SET `name`="jinzhu",`location`=ST_PointFromText("POINT(100 100)") WHERE `id` = 1

Update from SubQuery

Update a table by using SubQuery

1. db.Model(&user).Update("company_name", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id"))
2. // UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);

4. db.Table("users as u").Where("name = ?", "jinzhu").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id"))

6. db.Table("users as u").Where("name = ?", "jinzhu").Updates(map[string]interface{}{}{"company_name": db.Table("companies as c").Select("name").Where("c.id = u.company_id")})

Without Hooks/Time Tracking

If you want to skip Hooks methods and don’t track the update time when updating, you can use UpdateColumn, UpdateColumns, it works like Update, Updates

1. // Update single column
2. db.Model(&user).UpdateColumn("name", "hello")
3. // UPDATE users SET name='hello' WHERE id = 111;

5. // Update multiple columns
6. db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18})
7. // UPDATE users SET name='hello', age=18 WHERE id = 111;

9. // Update selected columns
10. db.Model(&user).Select("name", "age").UpdateColumns(User{Name: "hello", Age: 0})
11. // UPDATE users SET name='hello', age=0 WHERE id = 111;

Returning Data From Modified Rows

Return changed data, only works for database support Returning, for example:

1. // return all columns
2. var users []User
3. DB.Model(&users).Clauses(clause.Returning{}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
4. // UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING *
5. // users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}

7. // return specified columns
8. DB.Model(&users).Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
9. // UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING `name`, `salary`
10. // users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}

Check Field has changed?

GORM provides Changed method could be used in Before Update Hooks, it will return the field changed or not

The Changed method only works with methods Update, Updates, and it only checks if the updating value from Update / Updates equals the model value, will return true if it is changed and not omitted

1. func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
2. // if Role changed
3. if tx.Statement.Changed("Role") {
4. return errors.New("role not allowed to change")
5. }

7. if tx.Statement.Changed("Name", "Admin") { // if Name or Role changed
8. tx.Statement.SetColumn("Age", 18)
9. }

11. // if any fields changed
12. if tx.Statement.Changed() {
13. tx.Statement.SetColumn("RefreshedAt", time.Now())
14. }
15. return nil
16. }

18. db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu2"})
19. // Changed("Name") => true
20. db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu"})
21. // Changed("Name") => false, `Name` not changed
22. db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(map[string]interface{
23. "name": "jinzhu2", "admin": false,
24. })
25. // Changed("Name") => false, `Name` not selected to update

27. db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu2"})
28. // Changed("Name") => true
29. db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu"})
30. // Changed("Name") => false, `Name` not changed
31. db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(User{Name: "jinzhu2"})
32. // Changed("Name") => false, `Name` not selected to update

Change Updating Values

To change updating values in Before Hooks, you should use SetColumn unless it is a full updates with Save, for example:

1. func (user *User) BeforeSave(tx *gorm.DB) (err error) {
2. if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil {
3. tx.Statement.SetColumn("EncryptedPassword", pw)
4. }

6. if tx.Statement.Changed("Code") {
7. user.Age += 20
8. tx.Statement.SetColumn("Age", user.Age)
9. }
10. }

12. db.Model(&user).Update("Name", "jinzhu")