Gorm: How do I set an integer column to null, and update the model in-memory?

2
votes

As a simple background, I have a table foo, with a nullable int foreign key bar_id. I have a function that removes the bar association from foo, aka set it to NULL.

I have tried everything: I tried using sql.NullInt64 as the column type, and then 

foo.BarId.Valid = false // even set Int64 = 0 for good measure
db.Save(&foo) // with LogMode(true)

bar_id was not updated in the UPDATE statement

I tried:

db.Raw("UPDATE foo SET bar_id = NULL WHERE id = ?", foo.ID).Row().Scan(&foo)

The SQL was correct thankfully, but the object is not updated. I don't think my version of gorm has Error either.

I tried changing the type of Foo.BarId to an *int64, and setting the pointer to nil, but the output query didn't change bar_id to NULL.

What do I need to do?

3
gogo-gorm

3 Answers

1
votes

I got it. I need to specifically include the column in a Select():

db.Model(&foo).Select("bar_id").Updates(map[string]interface{}{"bar_id": nil})
0
votes

Another approach:

//RemoveAllChildren  remove all the children
 func RemoveAllChildren(db *gorm.DB, dataEdit Mat) (Mat, error) {
     var matupdate Mat
     db.Where("id = ?", dataEdit.ID).First(&matupdate)
     if err := db.Model(&matupdate).Update("children_mat", gorm.Expr("NULL")); err != nil {
             return matupdate, errors.New("cannotUpdate")
     }
     return matupdate, nil
 }

refer to: https://gist.github.com/thanhtungka91/186dca4cd14d30581bb98193153a55bf

0
votes

you can use gorm.Expr("NULL") also like below :

db.Model(&foo).Select("bar_id").Updates(map[string]interface{}{"bar_id": gorm.Expr("NULL")})

you can use this also on datetime database field type.