英文:
SQL COALESCE function is not working with GORM
问题
DB.Model(&domain.Products{}).Where("product_code", product.Product_Code).
Updates(map[string]interface{}{
"product_image": gorm.Expr("COALESCE(?, products.product_image)", product.Product_Image),
"size": gorm.Expr("COALESCE(?, products.size)", product.Size),
"color": gorm.Expr("COALESCE(?, products.color)", product.Color),
"unit_price": gorm.Expr("COALESCE(?, products.unit_price)", product.Unit_Price),
"stock": gorm.Expr("COALESCE(?, products.stock)", product.Stock),
})
这是一个使用gorm查询的代码，用于在更新表时管理空值插入。
我想通过保留表中的现有值来更新表，如果新值（传入的值）为空。
但是在我的代码中，表格更新为正常更新，这意味着空值和非空值都会像往常一样更新。我希望有人可以帮助我。
我还尝试了gorm原始查询，但也没有起作用。
ar.DB.Exec("UPDATE products SET size = COALESCE(?, size) WHERE product_code = ?", product.Size, product.Product_Code)
英文:
DB.Model(&domain.Products{}).Where("product_code", product.Product_Code).
Updates(map\[string\]interface{}{
"product_image": gorm.Expr("COALESCE(?, products.product_image)", product.Product_Image),
"size": gorm.Expr("COALESCE(?, products.size)", product.Size),
"color": gorm.Expr("COALESCE(?, products.color)", product.Color),
"unit_price": gorm.Expr("COALESCE(?, products.unit_price)", product.Unit_Price),
"stock": gorm.Expr("COALESCE(?, products.stock)", product.Stock),
})
It is a gorm query to manage null value insertion when we updating a table.
I want to update the table by keeping existing values there in the table if new value(incoming value) is null.
But here in my code the table updating as normal which means , both null values and not null values are updating as usual. I hope anyone can help me
I tried gorm raw query also. But it also not working
ar.DB.Exec("UPDATE products SET size = COALESCE(?, size) WHERE product_code = ?", product.Size, product.Product_Code)
答案1
得分: 0
COALESCE函数返回第一个非空参数。
为了使其工作，您需要提供指针。然而，从您的评论中的代码片段可以看出，没有提供指针。因此，_所有_指定的字段都将被更新。
具体来说，您的Products（或者在您的程序中叫什么都可以）类型应该像这样：
package domain
type Products struct {
Product_Code int `gorm:"not null"`
Product_Image *string
Size *int
Color *string
Unit_price *float64
Stock *int
}
请注意，字段被定义为指针。
然后可以像这样使用它：
newStock := 12
prod := domain.Products{
Product_Code: 42,
Product_Image: nil,
Size: nil,
Color: nil,
Unit_price: nil,
Stock: &newStock,
}
由于除了stock之外的所有字段都是nil，因此只有stock会在数据库中更新。
英文:
COALESCE returns the first argument that is not nil.
For this to work you need to supply pointers. However, as one can see from the code snippet in your comment, no pointers are supplied. Therefore, all specified fields will be updated.
Specifically, your Products (or whatever it is called in your program) type should look more like this:
package domain
type Products struct {
Product_Code int `gorm:"not null"`
Product_Image *string
Size *int
Color *string
Unit_price *float64
Stock *int
}
Note that fields are defined as pointers.
It would then be used like this:
newStock := 12
prod := domain.Products{
product_code: 42,
product_image: nil,
size: nil,
color: nil,
unit_price: nil,
stock: &newStock,
}
Sine all fields are nil with the exception of stock only stock will be updated in the database.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论